Technical Article

Find only characters or only integer from alphanumeric value

,

Change the above query as per need.

--Find only characters from string :

DECLARE @Temp VARCHAR(100)='548STA123Ldfgh45df45df5446fg54645dfg546';
Declare @NumRange AS varchar(50) = '%[0-9]%';
    While PatIndex(@NumRange, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1 ,'')

 SELECT @Temp

--====================================

Find only integers from string :

DECLARE @var VARCHAR(100)='5STA123Ldfgh45df45df5446fg54645dfg546';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9]%', @var )
END
END

Select ISNULL(@var,0)

--Find a float value from string :

DECLARE @var VARCHAR(100)='STA123.26dfg';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9.]%', @var )
END
END

Select ISNULL(@var,0)

Rate

5 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (4)

You rated this post out of 5. Change rating