Extracting last number within string-CodePudding

I have the below sample data and I'm trying to extract the last number within the string. I have the following, which gets me part of the way there, but I'm not sure how to get the value where it isn't the last word.

right(TextDescription, patindex('%[^0-9]%',reverse(TextDescription)) - 1)

The result should be:

ID	code
1	10015662
2	100040344
3	10015370
4	NULL
5	400337

Sample data

Create Table #TestData
(
    ID int,
    TextDescription varchar(100)
)

insert into #TestData Values (1,'Data From JOE BLOGGS 10015662 tree 10015662')
insert into #TestData Values (2,'Fast Data From JOHN SMITH 10004034 MARY SMITH 100040344 plant')
insert into #TestData Values (3,'Data In 10015370 pot JONES')
insert into #TestData Values (4,'Fast Data From LEE tree')
insert into #TestData Values (5,'Direct Data 106600 JANE GREEN 400337')

CodePudding user response：

Just another option using a a bit of JSON which will convert the string into an array and [key] will maintain the sequence.

Select A.ID 
      ,B.Value
 From  #TestData A
 Outer Apply (
                Select top 1 Value
                 From  OpenJSON( '["' replace(string_escape(TextDescription,'json'),' ','","') '"]' )
                 Where try_convert(int,Value) is not null
                 Order by [key] Desc
             ) B

Results

ID  Value
1   10015662
2   100040344
3   10015370
4   NULL
5   400337

CodePudding user response：

If your interesting values are always found at the end, and are always preceeded by a space, you can use the SUBSTRING with:

lower boundary being the last space before the last number location
length being the difference between last space and first value of last number

WITH cte AS (
    SELECT ID, TextDescription,
           PATINDEX('%[0-9] %', REVERSE(TextDescription)) AS first_space,
           PATINDEX('%[0-9]%', REVERSE(TextDescription))  AS last_digit 
    FROM #TestData
)  
SELECT ID, 
       SUBSTRING(TextDescription, 
                 LEN(TextDescription) -first_space  1,
                 first_space 1 -last_digit)              AS code
FROM #TestData

Check the demo here.