CODEWITHSUNDEEP
sqlsql-server
SarahChapman
SarahChapman

Reputation: 65

SQL Server - Extract text from a given string field

What is the best method to extract the text from the string field below?

I am trying to extract the ProjectID numbers (91, 108, 250) below but am struggling because the ProjectIDs are either 2 or 3 integers long and are within different parts of the string. 

Row   Parameter
1     ProjectID=91&GroupID=250&ParentID=1
2     ProjectID=108&GroupID=250&ParentID=35
3     GroupID=1080&ProjectID=250&ParentID=43
4     ProjectID=250

Any help would be much appreciated.

Upvotes: 0

Views: 74

Answers (2)

somaye javidmoradi
somaye javidmoradi

Reputation: 197

SELECT 
substring ( @Parameter, 
            CHARINDEX('ProjectID', @Parameter) + 10,
            CHARINDEX('&', @parameter, CHARINDEX('ProjectID', @Parameter)) - 
(CHARINDEX('ProjectID', @Parameter) + 10))
from table

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

SQL Server is kind of lousy on string functionality. Here is one method:

select left(v1.p, patindex('%[^0-9]%', v1.p + ' ') - 1)
from (values ('ProjectID=91&GroupID=250&ParentID=1'),
             ('ProjectID=108&GroupID=250&ParentID=35'),
             ('GroupID=1080&ProjectID=250&ParentID=43'),
             ('ProjectID=250')
     ) v(parameter) cross apply
     (values (stuff(v.parameter, 1, charindex('ProjectID=', v.parameter) + 9, ''))
     ) v1(p);

Or split the string and look for a match:

select stuff(s.value, 1, 10, '')
from (values ('ProjectID=91&GroupID=250&ParentID=1'),
             ('ProjectID=108&GroupID=250&ParentID=35'),
             ('GroupID=1080&ProjectID=250&ParentID=43'),
             ('ProjectID=250')
     ) t(parameter) cross apply
     string_split(t.parameter, '&') s
where s.value like 'ProjectId=%';

Here is a db<>fiddle.

Upvotes: 0

Related Questions