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 as this, if you want to select numeric value only from the alphanumeric string, use the 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

Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...