CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
user2995020
user2995020

Reputation: 1

Picking Number String out of Text String

I have a text field which has entries of variable length of the form:

"house:app.apx&resultid=1234,clientip"

or

"tost:app.apx&resultid=123,clientip"

or

"airplane:app.apx&resultid=123489,clientip"

I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers: 

1234
123
12389

the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.

Upvotes: 0

Views: 68

Answers (3)

Sam CD
Sam CD

Reputation: 2097

Similar to @Rahul but somewhat different and using PATINDEX:

declare @start varchar(20) = '%resultid=%'
declare @end varchar(20) = '%,clientid%'
SELECT REPLACE(REPLACE(SUBSTRING(Field,PATINDEX(@start,Field)),@start,''),@end,'')

Upvotes: 0

jpw
jpw

Reputation: 44871

This should work:

-- test data
DECLARE @t table (String VARCHAR(100))
INSERT @t VALUES 
('"house:app.apx&resultid=1234,clientip"'),
('"tost:app.apx&resultid=123,clientip"'),
('"airplane:app.apx&resultid=123489,clientip"')


SELECT 
  SUBSTRING(
    String, 
    CHARINDEX('resultid=', String) + 9, 
    CHARINDEX(',clientip', String) - CHARINDEX('resultid=', String) - 9
  ) 
FROM @t

-- Result
1234
123
123489

You might want to add some kind of check so that you don't process null values.

Upvotes: 1

Rahul
Rahul

Reputation: 77866

It can be done like below using string functions

select SUBSTRING("house:app.apx&resultid=1234,clientip",
                           CHARINDEX('=',"house:app.apx&resultid=1234,clientip"),
                           CHARINDEX(',',"house:app.apx&resultid=1234,clientip") - CHARINDEX('=',"house:app.apx&resultid=1234,clientip"));

Upvotes: 0

Related Questions