CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2017
Philip
Philip

Reputation: 2628

Extracting last number within string

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')

Upvotes: 1

Views: 298

Answers (5)

philip kanth
philip kanth

Reputation: 19

CREATE FUNCTION [ExtractInteger](@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''

WHILE @Count <= LEN(@String)
BEGIN

IF SUBSTRING(@String,@Count,1) = ' '
BEGIN
SET @IntNumbers = @IntNumbers + ' ' 
END

IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
SET @Count = @Count + 1
END

RETURN LTRIM(RTRIM(@IntNumbers))
END

The ExtractInteger function will fetch only numbers and spaces, and the below select will take the last word as number:

select right(dbo.ExtractInteger('My 3rd Phone Number is 323-111-CALL'), charindex(' ', reverse(dbo.ExtractInteger('My 3rd Phone Number is 323-111-CALL')) + ' ') - 1)

Upvotes: 1

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

Please try the following solution leveraging XML and XQuery.

Notable points:

  • CROSS APPLY is tokenizing TextDescription column as XML.
  • XQuery FLWOR expression is checking every token if it is castable as INTEGER data type. And filtering them out if they are not.
  • XPath predicate [last()] is giving us last INTEGER value.

SQL

-- DDL and sample data population, start
DECLARE @tbl Table (ID INT IDENTITY PRIMARY KEY, TextDescription varchar(100));
INSERT INTO @tbl (TextDescription) VALUES 
('Data From JOE BLOGGS 10015662 tree 10015662'),
('Fast Data From JOHN SMITH 10004034 MARY SMITH 100040344 plant'),
('Data In 10015370 pot JONES'),
('Fast Data From LEE tree'),
('Direct Data 106600 JANE GREEN 400337');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT t.*
    , c.query('for $x in /root/r[not(empty(xs:int(.)))]
        return $x
    ').value('(/r[last()]/text())[1]','INT') AS [code]
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
      REPLACE(TextDescription, @separator, ']]></r><r><![CDATA[') + 
      ']]></r></root>' AS XML)) AS t1(c)
ORDER BY t.ID;

Output

ID TextDescription code
1 Data From JOE BLOGGS 10015662 tree 10015662 10015662
2 Fast Data From JOHN SMITH 10004034 MARY SMITH 100040344 plant 100040344
3 Data In 10015370 pot JONES 10015370
4 Fast Data From LEE tree NULL
5 Direct Data 106600 JANE GREEN 400337 400337

Upvotes: 2

lemon
lemon

Reputation: 15482

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

  • lower boundary being the last non-digit before the last number location
  • length being the difference between last non-digit and first value of last number
WITH cte AS (
    SELECT ID, TextDescription,
           PATINDEX('%[0-9][^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 cte

Check the demo here.

Upvotes: 1

T N
T N

Reputation: 10023

The following will locate the last digit, trim the string at that point, find the preceding non-digit, and then perform another trim to get the final result. As in your original post, calculations are done on a reversed string to accommodate the lack of of a LASTPATINDEX() function. CROSS APPLY is used to build up intermediate results and to avoid duplication of subexpressions.

select T.*, P1.Pos1, P2.Pos2, N.Result
from #TestData T
cross apply (select reverse(TextDescription) AS Reversed) R
cross apply (select nullif(patindex('%[0-9]%', R.Reversed), 0) AS Pos1) P1
cross apply (select stuff(R.Reversed, 1, P1.Pos1 - 1, '') AS Trim1) T1
cross apply (select patindex('%[^0-9]%', T1.Trim1 + 'X') AS Pos2) P2
cross apply (select reverse(left(T1.Trim1, P2.Pos2 - 1)) AS Result) N

-- Partly reduced
select T.*, reverse(left(T1.Trim1, patindex('%[^0-9]%', T1.Trim1 + 'X') - 1)) AS Result
from #TestData T
cross apply (select reverse(TextDescription) AS Reversed) R
cross apply (select stuff(R.Reversed, 1, nullif(patindex('%[0-9]%', R.Reversed), 0) - 1, '') AS Trim1) T1

This will handle a variety of forms, not just space-delimited values.

See this db<>fiddle.

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81970

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

Upvotes: 3

Related Questions