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

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...