23 February 2008 10:50 Alex_Kuznetsov

Remove all non-numeric characters from a string.

The problem came up on newsgroups. The set based solution is quite simple:

SELECT REPLICATE('0', COUNT(*) - MAX(CASE WHEN c<>'0' THEN n ELSE 0
END))
+
CAST((SUM(CAST((c + LEFT('00000000000000000',n)) AS INT)) /10) AS
VARCHAR(100))
FROM(
SELECT c, ROW_NUMBER()OVER(ORDER BY Number DESC) AS n FROM(SELECT
SUBSTRING('asdf004506õÎÉÏÃÄÅ8sd',Number, 1) AS c, Number FROM
Data.Numbers) t
WHERE c IN('0','1','2','3','4','5','6','7','8','9')
) t

-----------------
0045068

 

It assumes that there is an auxiliary table Data.Numbers.

Comments

# re: Remove all non-numeric characters from a string.

25 February 2008 08:14 by Madhivanan

# re: Remove all non-numeric characters from a string.

26 February 2008 16:09 by Alex_Kuznetsov

# Remove All Non-Numeric Characters From A String

18 September 2008 19:54 by Strate SQL

I completely lack creativity today but thought this link on removing non-numeric characters was kind

# Remove All Non-Numeric Characters From A String &laquo; Strate SQL

Pingback from  Remove All Non-Numeric Characters From A String &laquo; Strate SQL