MSSQL : Удаление не цифровых символов в строке

Всем привет, Сегодня хочу поделиться результатом изысканий по классической задаче, удаление всех не нужных символов. Если мы говорили бы о функциональном программировании, то тут все просто, например так:

1. CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
2.     RETURNS VARCHAR(1000)
3. BEGIN
4.     DECLARE @pos INT
5.     SET @Pos = PATINDEX('%[^0-9]%',@Input)
6.     WHILE @Pos > 0
7.     BEGIN
8.         SET @Input = STUFF(@Input,@pos,1,'')
9.         SET @Pos = PATINDEX('%[^0-9]%',@Input)
10.     END
11.     RETURN @Input
12. END

Но, так как мы находимся в среде, SQL в моем случае MSSQL, то было найти решение не использующее скалярную функцию. Такое решение было найдено тут (http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/06eb934c-4082-4d12-ab9b-5fbe68855061/) Вот текст изначального примера:

1. DECLARE @test TABLE(x VARCHAR(20));
2. INSERT INTO @test (x)
3.     SELECT '+91 (876) 098 6789' UNION ALL
4.     SELECT '1-567-987-7655' UNION ALL
5.     SELECT 'FR5-5105';
7. WITH
8.     Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
9.     Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
10.     Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
11.     Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
12.     Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
13.     Cleaner AS (
14.         SELECT x
15.         ,   (SELECT
16.                 CASE
17.                     WHEN SUBSTRING(x, n, 1) NOT LIKE '[^0-9]'
18.                         THEN SUBSTRING(x, n, 1)
19.                     ELSE ''
20.                 END + ''
21.             FROM Nums
22.             WHERE n <= LEN(x)
23.             FOR XML PATH('')) AS x_clean
24.         FROM @test)
25. SELECT x, x_clean
26. FROM Cleaner;

1. /*
2. x x_clean
3. -------------------- -------------
4. +91 (876) 098 6789 918760986789
5. 1-567-987-7655 15679877655
6. FR5-5105 55105
7. */

На мой взгляд просто замечательный подход, но что нам делать если такая задача будет повторяться?! Не переписывать же под каждую задачу этот кусок (мне надо было 8 полей очистить в одном единственном запросе) И тут на помощь приходят табличные функции

1. CREATE FUNCTION fnt_RemoveNonNumericChars (@x varchar(50))
2.     RETURN TABLE
3. AS
4. RETURN (
5.     WITH
6.         Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
7.         Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
8.         Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
9.         Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
10.         Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
11.         Cleaner AS (
12. SELECT @x [x]
13. ,    (SELECT
14.         CASE
15.             WHEN SUBSTRING(@x, n, 1) NOT LIKE '[^0-9]'
16.                 THEN SUBSTRING(@x, n, 1)
17.             ELSE ''
18.         END + ''
19.     FROM Nums
20.     WHERE n <= LEN(@x)
21.     FOR XML PATH('')) AS x_clean
22.     )
23. SELECT x, x_clean
24. FROM Cleaner;
25. )

И вот собственно вариант использования:

1. DECLARE @test TABLE(x VARCHAR(20));
3. INSERT INTO @test (x)
4.     SELECT '+91 (876) 098 6789' UNION ALL
5.     SELECT '1-567-987-7655' UNION ALL
6.     SELECT 'FR5-5105';
8. SELECT
9.     t.x
10. ,   vv.x
11. ,   vv.x_clean
12. FROM @test t
13.     CROSS APPLY fnt_RemoveNonNumericChars(t.x) vv

И результат выполнения:

1. t.x vv.x vv.x_clean
2. -------------------- -------------------- -------------
3. +91 (876) 098 6789 +91 (876) 098 6789 918760986789
4. 1-567-987-7655 1-567-987-7655 15679877655
5. FR5-5105 FR5-5105 55105

ВАЖНО!!! Если будете использовать вариант с XML имейте в виду, порядок цифр может перемешаться, наткнулся на эту проблему буквально на следующий день.
В принципе мне не очень нравится текущий вариант нумерации, но это уже лирика, ее легко можно поменять, это позволит сэкономить еще несколько тактов, особенно на больших числах.