Find AlphaNumeric Characters Only from a String

     In SQL Server 2005, 2008 or 2012 if to find Numeric Characters only or AlphaNumeric Characters only from a given String is required then we can go for a user defined function to get the task done.

    To find only Numeric Characters, write the below user defined function:

CREATE FUNCTION dbo.udf_NumericChars

(

@String     VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

AS

BEGIN




  DECLARE @RemovingCharIndex INT

  SET @RemovingCharIndex = PATINDEX('%[^0-9]%',@String)




  WHILE @RemovingCharIndex > 0

  BEGIN

    SET @String =
STUFF(@String,@RemovingCharIndex,1,'')

    SET
@RemovingCharIndex = PATINDEX('%[^0-9]%',@String)

  END




  RETURN @String




END







------- For an
example ---------

SELECT DBO.udf_NumericChars('ADF[LJ64,;5AS90D')




-- Output

64590

    But for a function to get the AlphaNumeric characters you need a small change in PATINDEX as below :

CREATE FUNCTION dbo.udf_AlphaNumericChars 

(

@String     VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

AS

BEGIN




  DECLARE @RemovingCharIndex INT

  SET @RemovingCharIndex = PATINDEX('%[^0-9A-Za-z]%',@String)




  WHILE @RemovingCharIndex > 0

  BEGIN

    SET @String =
STUFF(@String,@RemovingCharIndex,1,'')

    @RemovingCharIndex =
PATINDEX('%[^0-9A-Za-z]%',@String)

  END




  RETURN @String




END







------- For an
example ---------

SELECT DBO.udf_AlphaNumericChars('ADF[LJ64,;5AS90D')




-- Output

ADFLJ645AS90D

Reference: Govind Badkur(http://sqlserver20.blogspot.com)