0

Adding onto the logic behind this forum responce, how would I select the first int with a specific set of trailing characters in a string? In other words, I would like to extract "15 in" out of "this thing is 15 in long!"

I've tried the following (based on the answer)...

SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1)
FROM (
     SELECT subsrt = SUBSTRING(string, pos, LEN(string))
     FROM (
          SELECT string, pos = PATINDEX('%[0-9] in%', string)
          FROM @temp
     ) d
) t

...but it will only extract "5 in" from the example string, instead of "15 in". So, not only do I need the whole int value (the original purpose of this script was to find the first whole int value in a string), but I need the whole int value that precedes "in" within a string.

I know this is a potential duplicate post and I'll add this as a comment to the original forum's answer once I get enough reputation, then remove this post if need be.

Community
  • 1
  • 1
JCBWS
  • 48
  • 1
  • 2
  • 9

1 Answers1

1

If you example is giving you "5 in" then add an additional "[0-9]" 

DECLARE @temp TABLE
(
      string NVARCHAR(50)
)

INSERT INTO @temp (string)
VALUES 
    ('bob this thing is 15 in long!')

SELECT SUBSTRING(string, pos, 5)
FROM (
    SELECT string, pos = PATINDEX('%[0-9][0-9] in%', string)
    FROM @temp
) d
Dbloch
  • 2,326
  • 1
  • 13
  • 15
  • Ah, so I would have to know the number of characters the whole int value would be if I wanted to use PATINDEX to find the whole int value preceding a certain string of characters? If that's the case, this solution will do just fine! Thanks Dbloch! – JCBWS Oct 06 '15 at 16:04
  • I believe you could do a range in front of the [0-9] if the number of digits vary. Google regex, I believe it would be something like {0,4}[0-9] which should pick from 0 to 4 digits – Dbloch Oct 06 '15 at 19:45