How to remove any character then number in SQL Server?

Question

I have a column with data like:

401-305-3069703-1---------------------------------------------------------
1-1499964-305-395---------------------------------------------------------
1-14995964-4405-395---------------------------------------------------------
....

How I can remove all dash and any character after last number like:

401-305-3069703-1
1-1499964-305-395
1-14995964-4405-395

Check this:https://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters — Prashant Pimpale, Apr 28 '19 at 06:33
thanks but i need 401-305-3069703-1 and - between number is non-numeric — dasdsadasdsad asdasdas, Apr 28 '19 at 06:35
Refer this one: https://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function — Prashanth, Apr 28 '19 at 06:48

score 4 · Accepted Answer · edited Apr 28 '19 at 07:23

You can try like following using PATINDEX and REVERSE

SELECT LEFT(@str, LEN(@str) - PATINDEX('%[0-9]%', REVERSE(@str)) +1)

Online Demo

Here PATINDEX is being used to find the last occurrence of any number using wildcard pattern, as there are multiple numbers and we want to find the last number index so we are reversing it using REVERSE before applying the PATINDEX.

Once the index is found, using LEFT we are getting the substring from the text.

How to remove any character then number in SQL Server?

1 Answers1