SQL String parsing and fetching certain value

Question

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

i dont even saw any problem with your code.. its working fine and return the billid.. — Vijunav Vastivch
– Vijunav Vastivch, Commented Jul 28, 2016 at 3:06

John Cappelletti · Accepted Answer · 2016-07-30 00:08:14Z

1

With the help of a Parser Function.

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'

-- Clean and Normalize String
Set @Str = Replace(Replace(Replace(@Str,'Bill id','BILLID'),'BILLID :','BILLID:'),'BILLID: ','BILLID:')
Select Distinct BillID=Replace(Key_Value,'BILLID:','') 
 From  [dbo].[udf-Str-Parse](@Str,' ') 
 Where Key_Value Like 'BILLID%'

Returns

BillID
AS12345
WE23456

The UDF

ALTER FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--       Select * from [dbo].[udf-Str-Parse]('id26,id46|id658,id967','|')
--       Select * from [dbo].[udf-Str-Parse]('hello world. It. is. . raining.today','.')

    Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
    As
    Begin
       Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
       Insert Into @ReturnTable Select Key_Value = ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
       Return 
    End

edited Jul 30, 2016 at 0:08

answered Jul 28, 2016 at 3:13

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

goofyui Over a year ago

Thank you John, I thought system functions such as Stuff can do parsing

John Cappelletti Over a year ago

@goofyui I'll noodle it a bit more, Perhaps there is a more elegant way

John Cappelletti Over a year ago

@goofyui Full-Disclosure. Your process revealed a bug in my oringnal parser. When two consecutive IDENTICAL strings , only one would be returned. Since you only wanted distinct, it wouldn't matter for this test, HOWEVER, the parser should be correct as a foundation helper. The UPDATED function listed above has the SAME FUNCTIONALITY and should be updated on your system.

John Cappelletti Over a year ago

@goofyui I should also add, it is faster as well.

samm · Accepted Answer · 2016-07-28 03:04:12Z

0

I think a custom function using the regular expression id:\s*[A-Z][A-Z][0-9]+.

answered Jul 28, 2016 at 3:04

samm

82011 silver badges25 bronze badges

1 Comment

goofyui Over a year ago

keyword to filter the statement is bill id:

Collectives™ on Stack Overflow

SQL String parsing and fetching certain value

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related