If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.
Course Of The Month
6 days, 11 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL String parsing and fetching certain value
Posted on 2016-07-27
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
[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
2
- +1
5 Comments
Active 1 day ago
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
Active 1 day ago
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:
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.
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.
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+'%'
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+'%'
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.
Active today
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month6 days, 11 hours left to enroll
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.