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.