Select first int before characters in string

Question

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.

Answer 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