Function to parse alphanumeric characters from string in SQL Server

Alphanumeric string consists of alphabets and numbers for example a string 123xyz456abc is an alphanumeric string. Many a times it is required to extract only alphanumeric characters from a string excluding special characters, this blog post provides a function to parse alphanumeric characters from string in SQL Server

CREATE FUNCTION [dbo].fn_parsealphanumericstring 
(
    @string nvarchar(max)
)
RETURNS TABLE AS RETURN
(
WITH T1(number) AS (SELECT 1 UNION ALL SELECT 1),
T2(number)  AS (SELECT 1 FROM T1 AS a cross join T1 as b),
T3(number)  AS (SELECT 1 FROM T2 AS a cross join T2 as b),
T4(number)  AS (SELECT 1 FROM T3 AS a cross join T3 as b),
Nums(number) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
from T4)
SELECT STUFF( 
(SELECT   '' + SUBSTRING(@String,Nums.number,1)
FROM Nums
WHERE  PATINDEX('%[^0-9A-Za-z]%', SUBSTRING(@String,Nums.number,1)) =0
FOR XML PATH('')),1,0,'') As AlpahNumericString
)

The above query creates an inline table valued function to extract alphanumeric characters. The logic is same as explained here. however, instead of using ISNUMERIC to check of numeric values I have used PATINDEX to identify alphanumeric character.

The function can be used as shown below.

DECLARE @test TABLE 
(
	Sno int identity,
	string nvarchar(MAX)
)
INSERT INTO @test
SELECT '##XYZ123""^&(767)*@#$$#'
UNION
SELECT '#@$%XYZ123--&$((767)*@#$$#PQR'
UNION
SELECT '"11/3"-BVG<>/123!~@*@#$$#ZIM'

SELECT t.Sno,t.string, an.AlpahNumericString
FROM @test t 
CROSS APPLY dbo.fn_parsealphanumericstring(string) an;

The output from above query is shown below

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook