Dec 24, 2014

Query to select text only from alphanumeric string in SQL Server


If you want to select text only from alphanumeric string use following query



DECLARE @str VARCHAR(400)

DECLARE @expres  VARCHAR(50) = '%[0-9]%'

SET @str = 'Dilip 007 Singh'

WHILE PATINDEX( @expres, @str ) > 0

BEGIN

       SET @str = REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),'')

 END

SELECT @str





TEXTONLY

-------------

Dilip Kumar



Same like this if you want to select numeric value only from the alphanumeric string use below query.

DECLARE @str VARCHAR(400)

DECLARE @expres  VARCHAR(50) = '%[a-z]%'

SET @str = 'Dilip 007 Singh'

WHILE PATINDEX( @expres, @str ) > 0

BEGIN

       SET @str =REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),'')

END

SELECT @str AS NUMERICONLY



Result

NUMERICONLY

--------------------

 007