DECLARE @test TABLE(x VARCHAR(20));
INSERT INTO @test
SELECT '+91 (876) 098 6789' UNION ALL
SELECT '1-567-987-7655' UNION ALL
SELECT 'FR5-5105';
declare @n char(26) = '22233344455566677778889999'
;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
Cleaner AS (
SELECT x, (SELECT CASE WHEN SUBSTRING(x, n, 1) LIKE '[A-Za-z]' Then SUBSTRING(@n, (ASCII(upper(SUBSTRING(x, n, 1)))-64),1)
WHEN SUBSTRING(x, n, 1) LIKE '[0-9]'
THEN SUBSTRING(x, n, 1)
ELSE ''
END + ''
FROM Nums
WHERE n <= LEN(x)
FOR XML PATH('')) AS x_clean
FROM @test)
SELECT x, x_clean
FROM Cleaner;
/*
x x_clean
+91 (876) 098 6789 918760986789
1-567-987-7655 15679877655
FR5-5105 3755105
*/
This entry was posted
on Thursday, June 13, 2013
and is filed under
SQL Server,
Tools
.
You can leave a response
and follow any responses to this entry through the
Subscribe to:
Post Comments (Atom)
.
0 comments