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

Function to parse alphanumeric characters from string in SQL Server

 

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

   

2 Comments on “Function to parse alphanumeric characters from string in SQL Server”

  1. Hi,

    I have used your function but i am facing one issue like below order.

    PBLADPSY001
    PBLADPSY0011
    PBLADPSY0012
    PBLADPSY001A
    PBLADPSY001B
    PBLADPSY0022
    PBLADPSY002A
    PBLADPSY002B

    But I need like below order.

    PBLADPSY001
    PBLADPSY0011
    PBLADPSY0012
    PBLADPSY0022
    PBLADPSY001A
    PBLADPSY001B
    PBLADPSY002A
    PBLADPSY002B

    Please help me.

Leave a Reply

Your email address will not be published.