SQL remove characters from string and leave number only

Question

I have string(nvarchar) from db data and I would like to transfer it to numbers only. I was searching on Google for solution but I didnt find anything. I found something similiar here on StackOverflow but everything was removing characters only from left side, but if there is any character on right side or between numbers it wont work.

Solution I found but is not working:

select substring(XX, 
       PatIndex('%[0-9]%', XX), 
       len(XX))

For example I have text: '4710000 text' so this substring returns me same text I putted inside of it which is again '4710000 text'. Is there any other way how to do that? Without creating functions or using IFs, begins, variables (@text etc.).

Which dbms are you using? (The solution will probably be product specific.) — jarlh, Dec 02 '22 at 12:27
https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, Dec 02 '22 at 12:29
On some databases (not SQL Server), you could use a regex replacement: `REGEXP_REPLACE(XX, '\D+', '')` — Tim Biegeleisen, Dec 02 '22 at 12:32
Do you simply want to remove all non-digit characters? What's the expected result for '12ab 3 -4c5.6e7'? — jarlh, Dec 02 '22 at 12:46

score 2 · Accepted Answer · answered Dec 02 '22 at 17:01

Try this, it seems to work like a charm. I wish I could take credit but it's from this post. If it works for you please give him the upvote.

The 'with' is just a CTE that sets up test data.

with tbl(str) as (
  select '4710000 text'
)
SELECT
    (SELECT CAST(CAST((
        SELECT SUBSTRING(str, Number, 1)
        FROM master..spt_values
        WHERE Type='p' AND Number <= LEN(str) AND
            SUBSTRING(str, Number, 1) LIKE '[0-9]' FOR XML Path(''))
    AS xml) AS varchar(MAX)))
FROM
    tbl

score 1 · Answer 2 · answered Dec 02 '22 at 12:53

If you are using SQL Server and a fully supported version you can use translate like so:

select Replace(Translate('4710000 text', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', Replicate('*', 26)), '*', '');

If you have additional non-numerical characters add those in to the string and amend 26 accordingly.

SQL remove characters from string and leave number only

2 Answers2