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

Starting after a pattern &ID=
Ending till the last character or
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

Should the second line in section "What I need is" = "S-10758"? Currently both lines have same value "Z-12345". — rr789
– rr789, Commented Mar 28, 2022 at 21:33

Macbric · Accepted Answer · 2017-10-19 14:05:07Z

19

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

edited Oct 19, 2017 at 14:05

Macbric

4824 silver badges10 bronze badges

answered Sep 17, 2016 at 1:25

nichitaC

3212 silver badges4 bronze badges

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

1 Comment

hannes101 Over a year ago

I would like to add a caveat in case PATINDEX does not find the pattern, it defaults to 0, which might lead to unwanted strings being extracted.

James Z · Accepted Answer · 2015-02-04 04:09:59Z

5

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.

answered Feb 4, 2015 at 4:09

James Z

12.3k10 gold badges27 silver badges48 bronze badges

Collectives™ on Stack Overflow

SQL Server String extract based on pattern

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related