Picking Number String out of Text String

Question

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.

Is this data you are retrieving from the database or are you trying to do this in a stored procedure within the database? — MiltoxBeyond
– MiltoxBeyond, Commented Jul 14, 2015 at 19:04

jpw · Accepted Answer · 2015-07-14 19:11:55Z

1

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.

answered Jul 14, 2015 at 19:11

jpw

45k6 gold badges71 silver badges90 bronze badges

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

Comments

Rahul · Accepted Answer · 2015-07-14 19:09:41Z

0

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"));

answered Jul 14, 2015 at 19:09

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

Sam CD · Accepted Answer · 2015-07-14 19:20:24Z

0

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,'')

answered Jul 14, 2015 at 19:20

Sam CD

2,1072 gold badges20 silver badges36 bronze badges

Collectives™ on Stack Overflow

Picking Number String out of Text String

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related