Formatting columns with a sql query

RunDown BassMan

12y1.3k

Hey, I'm querying a table for a reference number. This seemed easy but these reference numbers are in the same cell as a description. Here is a example of a cell - CARRY OUT WORK AS DETAILED IN P.I.M.I. NO.E6801 001 & COMPLETE CHECK SHEET E6801 001RA
NOTE:.ENSURE PATOL SYSTEM SET TO....

All I want is the P.I.M.I. NO.E6801 001 number.

Is there any way of formatting a select query to only show these numbers? I don't really want to format the data itself as it will effect our document management system, just the query result.

B.T.W - I'm using Microsoft SQL server

Thank you.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Guest User12y ago
I had given you below

select substring(rec,charindex('P.I.M.I.',rec),charindex('&',rec)-charindex('P.I.M.I.',rec)) from t

understand its mechanics how it functions. It's assuming a pattern because you must have pattern to find data, e.g.,
- is it lenght starting from char 5 to 10
- is it lenght starting from char 5 till any space or char is encountered
- is it lenght starting from any char till any space or char is encountered (I used this..)

charindex('P.I.M.I.',rec) - will find this occurrence, e.g., output is 5
charindex('&',rec)-charindex('P.I.M.I.',rec) - will find & , e.g., output is 15

Now what i'm doing is picking substring from 15-5 = 10 this is ur 10 characters
P.I.M.I. No.....

what is your logic that you want to pick data? we can construct query based on that

twitter @sumitjolly
RunDown BassMan12y ago

Thanks, but its not entirely accurate When I ran this today, It did not pick up some of the PIMI numbers for some reason
Any ideas why?
Thank you
Guest User12y ago
I created table like this:

create table t (rec nvarchar(max))
GO
insert into t values('CARRY OUT WORK AS DETAILED IN P.I.M.I. NO.E6801 001 & COMPLETE ')
GO
insert into t values('CARRY OUT WORK AS IN P.I.M.I. NO.E6802 002 & COMPLETE ')

Then I have written query like this to get data from P.I.M.I. till 001

select substring(rec,charindex('P.I.M.I.',rec),charindex('&',rec)-charindex('P.I.M.I.',rec)) from t

twitter @sumitjolly