How to select the number in a string in SQL Server

Question

I have a column which values is of NVARCHAR(MAX) type. It has following structure:

"Key1=Value1-Key2=Value2-...Key_N=Value_N"

For eg:

"Type=A-SRID=152-WOID=3"

Here it has 3 key-value pairs: {Type: A, SRID: 152, WOID: 3}

What I am trying to do is to extract the value of key named "SRID", it may OR may not exist in the string. If it exists, its value also exists and must be integer.

So using the example string above, "152" should be selected. If the key does not exist, either null or empty string is selected, both are acceptable.

How to acheive this, if possible, with one single select query?

Will SRID always follow a -? Will Type and WIOD always exist? Cn a value ever contain a -? Please post some more examples of all the different cases.The trick is not finding the data, it's covering all the edge cases — Nick.Mc
– Nick.Mc, Commented Mar 9, 2017 at 2:31
Fix your data structure if you can. This is definitely not a convenient way to store data in a SQL database. — Gordon Linoff
– Gordon Linoff, Commented Mar 9, 2017 at 2:31
Have you considered mangling the string into XML and performing a query thereon? — HABO
– HABO, Commented Mar 9, 2017 at 2:34
@Nick.McDermaid The Key=Value pair can be of any order, so SRID can be the first one in the string. — shole
– shole, Commented Mar 9, 2017 at 2:42
@GordonLinoff I know...it is legacy one and I am trying to do some data patching on it without permission on changing it... — shole
– shole, Commented Mar 9, 2017 at 2:43

John Cappelletti · Accepted Answer · 2017-03-09 02:38:01Z

2

Just about any parser will do

Option 1: with UDF

Declare @YourTable table (ID int,YourCol varchar(max))
Insert Into @YourTable values
(1,'Type=A-SRID=152-WOID=3')


Select A.ID
      ,Item  = left(B.RetVal,charindex('=',B.RetVal+'=')-1)
      ,Value = substring(B.RetVal,charindex('=',B.RetVal+'=')+1,len(B.RetVal))
 From  @YourTable A
 Cross Apply [dbo].[udf-Str-Parse](A.YourCol,'-') B
 --Where B.RetVal like 'SRID%'

Option 2: Without a UDF

Select A.ID
      ,Item  = left(B.RetVal,charindex('=',B.RetVal+'=')-1)
      ,Value = substring(B.RetVal,charindex('=',B.RetVal+'=')+1,len(B.RetVal))
 From  @YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(A.YourCol,'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             ) B
  --Where B.RetVal like 'SRID%'

Both Return

ID  Item    Value
1   Type    A
1   SRID    152
1   WOID    3

The UDF if Needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--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]('this,is,<test>,for,< & >',',')
--Performance On a 5,000 random sample -8K 77.8ms, -1M 79ms (+1.16), -- 91.66ms (+13.8)

answered Mar 9, 2017 at 2:38

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

3 Comments

shole Over a year ago

Option 2 works for me, though it's too complicated for me...will try to understand it

John Cappelletti Over a year ago

@shole Same code as in the UDF, the XML parser is surprisingly fast. On a 5,000 row sample only 13 ms slower than the tally parse. Tally can not be slipped into a Cross Apply and is limited to 8K bytes. Just think of a Cross Apply as a sub-routine. cheers :)

John Cappelletti Over a year ago

@shole just an after thought. OUTER APPLY will show null records if needed.

MPR · Accepted Answer · 2017-03-09 03:10:33Z

Everyone who has said the edge cases are the trick is correct, but if the structure is consistent this should work as well with simple string functions. Example with three tests:

declare @test varchar(max) = 'Type=A-SRID=152-WOID=3'
declare @test2 varchar(max) = 'SRID=152-WOID=3'
declare @test3 varchar(max) = 'Type=A-WOID=3'

select iif(charindex('SRID=', @test) > 0, substring(@test, charindex('SRID=', @test)+5, charindex('-',substring(@test, charindex('SRID=', @test)+5, 8000))-1),'')
select iif(charindex('SRID=', @test2) > 0, substring(@test2, charindex('SRID=', @test2)+5, charindex('-',substring(@test2, charindex('SRID=', @test2)+5, 8000))-1),'')
select iif(charindex('SRID=', @test3) > 0, substring(@test3, charindex('SRID=', @test3)+5, charindex('-',substring(@test3, charindex('SRID=', @test3)+5, 8000))-1),'')

Edit: you are correct you need handling for if the string ends after the value you are looking for, but in that case a simple case statement will handle it. It can still be done with string functions:

select case when charindex('-',substring(@test, charindex('SRID=', @test)+5, 8000))-1 < 0
        then substring(@test, charindex('SRID=', @test)+5, 8000)
        else iif(charindex('SRID=', @test) > 0, substring(@test, charindex('SRID=', @test)+5, charindex('-',substring(@test, charindex('SRID=', @test)+5, 8000))-1),'')
        end

Collectives™ on Stack Overflow

How to select the number in a string in SQL Server

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related