Question: 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

* SB124197 --> 124197
* 287276ACBX92 --> 92
* R009321743-16 --> 16

How to achieve this functionality. Please help.

Answer: 
Try this:

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

```

**Explanation:**

Using [**PATINDEX**](https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql) 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**](http://sqlfiddle.com/#!6/a51ab/3)
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

```