I had to migrate data from legacy system to new system and I found that we have some fields having alphanumeric data along with special character. The target was to extract the alpha numeric data from string. So, I started with web research and found few solutions but most of them are cursor based but I would like to develop it without cursor to improve the performance.
Given below is the solution.
CREATE FUNCTION dbo.[UDF_Extract_Alphanumeric_From_String]
(
@String VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)
; WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2(n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3(n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4(n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)
SELECT @RETURN_STRING=ISNULL(@RETURN_STRING,'')+ SUBSTRING(@String,Nums.n,1)
FROM N4 Nums
WHERE Nums.n<=LEN(@String) AND PATINDEX('%[0-9A-Za-z ]%',SUBSTRING(@String,Nums.n,1))>0
RETURN @RETURN_STRING
END
GO
SELECT dbo.[UDF_Extract_Alphanumeric_From_String] ('This! is a t_est s/tring a_t ra.re:s;ql') as [Result]
--OUTPUT
Result
————————————-
This is a test string at raresql
(1 row(s) affected)
SQL Server Portal 