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
