Formatting columns with a sql query

Question

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.

Sumit Jolly · Answer

I had given you below select substring(reccharindex('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 BassMan · Answer

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

Sumit Jolly · Answer

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(reccharindex('P.I.M.I.'rec)charindex('&'rec)-charindex('P.I.M.I.'rec)) from t twitter @sumitjolly