SQL String parsing and fetching certain value

I have a string which i have to parse and retrieve the bill id fields. My string can have either one bill id or multiple bill id's.



Declare @str nvarchar(1000)

set @Str = 'Process of submitting the bill id: AS12345 is send back to Customer. Bill id:WE23456 and Bill id: AS12345 came from customers'

Declare @t table (billid varchar(100))
Insert into @t values ('AS12345'),('WE23456'),('AS12345')

select distinct billid from @t

Open in new window

chokkaStudentAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
not the prettiest but here goes:
Declare @str nvarchar(1000)

set @Str = 'Process of submitting the bill id: AS12345 is send back to Customer. Bill id:WE23456 and Bill id: AS12345 came from customers'

Declare @t table (billid varchar(100))
Insert into @t values ('AS12345'),('WE23456'),('AS12345')

select distinct billid from @t;

WITH cte AS (
SELECT @str base, PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',@str) start, SUBSTRING(@str,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',@str),7) BillId, SUBSTRING(@str,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',@str)+7,LEN(@str)) remaining
UNION ALL
SELECT cte.remaining base, PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',cte.remaining) start, SUBSTRING(cte.remaining,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',cte.remaining),7) BillId, SUBSTRING(cte.remaining,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',cte.remaining)+7,LEN(cte.remaining)) remaining
FROM cte
WHERE PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',cte.remaining) <> 0
)
SELECT DISTINCT cte.BillId
FROM cte

Open in new window

0
 
Olaf DoschkeSoftware DeveloperCommented:
Chris is trying to etract all parts of @Str resembling a bill id pattern. That's fine, what this doesn't do is compare with really existing bill ids.

There's a simple solution without recursive cte, but it'll have a performance disadvantage with a large bill table:

Select bill.billid from @t as bill 
where @Str Like '%'+bill.billid+'%'

Open in new window


You'd better be able to reduce the amount of bill ids to check, by having a flag of open bills you want to check and skip all the bills already closed for weeks, months, even years.

Select bill.billid from @t as bill
where bill.IsOpen AND @Str Like '%'+bill.billid+'%'

Open in new window


This solution is checking every reevant)billid, whether being part of @Str or not.

Tihs could combined, eg you could take Chris resut and inner join it with bills to see what he fishes really are bills and not any other ZZ99999 pattern. His solution also lacks finding any bill ids with more or less digits.

Bye, Olaf.
0
 
SharathData EngineerCommented:
you can try this. Assumption is you always have : before the billid. 
Declare @str nvarchar(1000)

set @Str = 'Process of submitting the bill id: AS12345 is send back to Customer. Bill id:WE23456 and Bill id: AS12345 came from customers'

Declare @t table (billid varchar(100))
Insert into @t values ('AS12345'),('WE23456'),('AS12345')

;WITH CTE AS (
SELECT RTRIM(LEFT(str1,CHARINDEX(' ',str1)))  billid FROM (
SELECT ltrim(SUBSTRING(str1, n, CHARINDEX(':', str1 + ':',n) - n)) AS str1
 FROM (SELECT @Str str1) t1
CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
WHERE SUBSTRING(':' + str1, n, 1) = ':'
  AND n < LEN(str1) + 1) t2)
SELECT DISTINCT t1.billid
  FROM CTE c1
  JOIN @t t1 ON c1.billid = t1.billid

Open in new window

0
 
chokkaStudentAuthor Commented:
I am always thankful to you. A lot to learn and learning from you !!
0
 
Chris LuttrellSenior Database ArchitectCommented:
thanks, glad you are learning things.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.