CODEWITHSUNDEEP
sql-server
user3125637
user3125637

Reputation: 51

SQL Server - How to get last numeric value in the given string

I am trying to get last numeric part in the given string.

For Example, below are the given strings and the result should be last numeric part only

How to achieve this functionality. Please help.

Upvotes: 3

Views: 8451

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

select right(@str, patindex('%[^0-9]%',reverse(@str)) - 1)

Explanation:

Using PATINDEX with '%[^0-9]%' as a search pattern you get the starting position of the first occurrence of a character that is not a number.

Using REVERSE you get the position of the first non numeric character starting from the back of the string.

Edit:

To handle the case of strings not containing non numeric characters you can use:

select case 
          when patindex(@str, '%[^0-9]%') = 0 then @str
          else right(@str, patindex('%[^0-9]%',reverse(@str)) - 1)
       end

If your data always contains at least one non-numeric character then you can use the first query, otherwise use the second one.

Actual query:

So, if your table is something like this:

mycol
--------------
SB124197
287276ACBX92
R009321743-16
123456

then you can use the following query (works in SQL Server 2012+):

select iif(x.i = 0, mycol, right(mycol, x.i - 1))
from mytable
cross apply (select patindex('%[^0-9]%', reverse(mycol) )) as x(i)

Output:

mynum
------
124197
92
16
123456

Demo here

Upvotes: 9

Pரதீப்
Pரதீப்

Reputation: 93724

Here is one way using Patindex

SELECT RIGHT(strg, COALESCE(NULLIF(Patindex('%[^0-9]%', Reverse(strg)), 0) - 1, Len(strg)))
FROM   (VALUES ('SB124197'),
               ('287276ACBX92'),
               ('R009321743-16')) tc (strg)

After reversing the string, we are finding the position of first non numeric character and extracting the data from that position till the end..

Result :

-----
124197
92
16

Upvotes: 3

Related Questions