Picking Number String out of Text String

• scotsditch

  Ten Centuries

  Points: 1020

  More actions

  July 14, 2015 at 12:59 pm

  #319779

  Hi 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.

  Any tips greatly appreciated.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  July 14, 2015 at 1:12 pm

  #1812446

  scotsditch (7/14/2015)

  ---

  Hi 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.

  Any tips greatly appreciated.

  You can do this fairly easily with a combination of SUBSTRING and CHARINDEX.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 14, 2015 at 1:18 pm

  #1812448

  Like this:

  with SampleData as (

  select TestData from

  (values ('house:app.apx&resultid=1234,clientip'),('tost:app.apx&resultid=123,clientip'),('airplane:app.apx&resultid=123489,clientip'))dt(TestData)

  )

  select

  substring(TestData,charindex('=',TestData) + 1,charindex(',clientip',TestData) - charindex('=',TestData) - 1),

  TestData

  from SampleData;

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 14, 2015 at 1:23 pm

  #1812452

  scotsditch (7/14/2015)

  ---

  Hi 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.

  Any tips greatly appreciated.

  Try this:

  DECLARE @STRINGS AS TABLE (

  STRING varchar(50)

  )

  INSERT INTO @STRINGS (STRING)

  SELECT 'house:app.apx&resultid=1234,clientip' UNION ALL

  SELECT 'tost:app.apx&resultid=123,clientip' UNION ALL

  SELECT 'airplane:app.apx&resultid=123489,clientip'

  SELECT S.STRING, SUBSTRING(S.STRING, CHARINDEX('resultid=', S.STRING) + 9, CHARINDEX(',clientip', S.STRING, CHARINDEX('resultid=', S.STRING) + 9) - (CHARINDEX('resultid=', S.STRING) + 9)) AS NUM

  FROM @STRINGS AS S

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 14, 2015 at 1:34 pm

  #1812455

  I actually prefer this format.

  SELECT S.STRING, LEFT( t.newString, CHARINDEX(',', t.newString) - 1) AS NUM

  FROM @STRINGS AS S

  CROSS APPLY (SELECT SUBSTRING(S.STRING, CHARINDEX('resultid=', S.STRING) + 9, 8000)) t(newString)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  July 14, 2015 at 10:07 pm

  #1812529

  Have a look at this thread from last year

  😎