Skip to main content

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


Result:
TEXT ONLY
-------------
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:
NUMERIC ONLY
--------------------

 007