Saturday, February 23, 2008 10:50 AM 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.

Monday, February 25, 2008 8:14 AM by Madhivanan

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

Tuesday, February 26, 2008 4:09 PM by Alex_Kuznetsov

# Remove All Non-Numeric Characters From A String

Thursday, September 18, 2008 7:54 PM 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