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, Jul 14 '15 at 19:04
[What have you tried](http://mattgemmell.com/2008/12/08/what-have-you-tried/)? — HABO, Jul 14 '15 at 19:54

score 1 · Answer 1 · answered Jul 14 '15 at 19:11

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.

score 0 · Answer 2 · answered Jul 14 '15 at 19:09

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

score 0 · Answer 3 · answered Jul 14 '15 at 19:20

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 '15 at 19:20

Sam Cohen-Devries

2,025
2
18
35

Picking Number String out of Text String

3 Answers3