Swim in SQL server

Posts tagged ‘SUBSTRING’

How to separate Integers from String using T-SQL

Here we can see different methods to separate Integers and String data types from a string variable.

SET NOCOUNT ON

declare @str varchar(100) = 'A1B2C3D4'
DECLARE @I INT = 1,@var varchar(100)='' , @int Varchar(100) = ''

WHILE @I <= LEN(@str)
begin
-- 48 Is ASCII Key for '0' and 57 is for '9'
--Checking Each char is between 0 to 9 or not. If yes then concatenation to @int variable. Else concatenation with @var Variable.
IF @I = 1 SELECT @str 'Input String' , SUBSTRING(@STR,@I,1) 'First Char of the Input String', ASCII(SUBSTRING(@STR,@I,1))  AS 'ASCII Value of the First Char'

IF ASCII(SUBSTRING(@STR,@I,1)) BETWEEN 48 AND 57
--IF PATINDEX('[^0-9-]',SUBSTRING(@STR,@I,1) ) = 0
BEGIN
IF @I = 1 SELECT @str , SUBSTRING(@STR,@I,1), ASCII(SUBSTRING(@STR,@I,1))
SET @int += SUBSTRING(@STR,@I,1)+','
END
ELSE
BEGIN
SET @var += SUBSTRING(@STR,@I,1)+','
END
set @I +=1
end
SELECT @str 'Input String','Integers'= SUBSTRING(@int,1,LEN(@int)-1) , REVERSE(STUFF(REVERSE(@var),1,1,'')) AS [String]
---------------------------Method 2----------------------------
/*
Inserting each character into @TBL table.
Using with CASE Statement displaying and Checking CHAR is Integer or String.
*/
SELECT @I = LEN(@Str)
DECLARE @TBL TABLE (ID INT IDENTITY , [Char] CHAR(1))
WHILE @I >= 1
begin
INSERT INTO @TBL SELECT SUBSTRING(@STR,@I,1)
set @I -=1
end

SELECT [Char],CASE WHEN PATINDEX('%[^0-9-]%',  [Char] ) = 0 THEN 'Integer' ELSE 'Strings' END AS [TYPE]
FROM @TBL
ORDER BY    [TYPE], ID  DESC
--------------------------------Method 3-------------------
SELECT Integers , Strings  FROM
(
SELECT ID, [Char],CASE WHEN PATINDEX('%[^0-9-]%',  [Char] ) = 0 THEN 'Integers' ELSE 'Strings' END AS [TYPE]
FROM @TBL
)  AS P
PIVOT(MAX(CHAR) FOR [TYPE] IN (Integers,Strings)  ) AS PVT
ORDER BY Integers  , Strings