?
Solved

SQL String parsing and fetching certain value

Posted on 2016-07-27
5
Medium Priority
?
57 Views
Last Modified: 2016-07-30
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

0
Comment
Question by:chokka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 41732353
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41732392
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
 
LVL 41

Expert Comment

by:Sharath
ID: 41732414
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
 

Author Closing Comment

by:chokka
ID: 41735344
I am always thankful to you. A lot to learn and learning from you !!
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 41736118
thanks, glad you are learning things.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question