SQL Server String extract based on pattern

Question

I have string data in the following format:

MODELNUMBER=Z12345&HELLOWORLD=WY554&GADTYPE=PLA&ID=Z-12345
/DTYPE=PLA&ID=S-10758&UN_JTT_REDIRECT=UN_JTT_IOSV

and need to extract IDs based on two conditions

1. Starting after a pattern &ID=

2. Ending till the last character or

3. if it hits a & stop right there.

So in the above example I'm using the following code:

SUBSTRING(MyCol,(PATINDEX('%&id=%',[MyCol])+4),(LEN(MyCol) - PATINDEX('%&id%',[MyCol])))

Essentially looking the pattern &id=% and extract string after that till end of the line. Would anyone advise on how to handle the later part of the logic ..

My current results are

Z-12345
Z-12345&UN_JTT_REDIRECT=UN_JTT_IOSV

What I need is

Z-12345
Z-12345

Answer 1

Try this

SUBSTRING(MyCol, (PATINDEX('%[A-Z]-[0-9][0-9][0-9][0-9][0-9]%',[MyCol])),7) 

if you run into performance issues add the where clause below

-- from Mytable
WHERE [MyCol] like '%[A-Z]-[0-9][0-9][0-9][0-9][0-9]%'

maybe not the most elegant solution but it works for me.

Correct syntax of PATINDEX

Answer 2

Here's one example how to do it:

select
    substring(d.data, s.s, isnull(nullif(e.e,0),2000)-s.s) as ID, 
    d.data 
from data d
cross apply (
    select charindex('&ID=', d.data)+4 as s
) s
cross apply (
    select charindex('&', d.data, s) as e
) e
where s.s > 4

This assumes there data column is varchar(2000) and the where clause leaves out any rows that don't have &ID=

The first cross apply searches for the start position, the second one for the end. The isnull+nulliff in the actual select handles the case where & is not found and replaces it with 2000 to make sure the whole string is returned.