SQL remove characters from string and leave number only-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.