Sometime we need to split a comma separated string to a Table of integers. The below function will split the given comma-separated string into integers and process the results.
CREATE Function SplitStringtoInt(@strTemp nVarChar(4000)) Returns @intTable Table([Value] [Int] NOT NULL)
AS
BEGIN
DECLARE @intValue nVarChar(100)
DECLARE @pos int
-- TRIMMING THE BLANK SPACES
SET @strTemp = LTRIM(RTRIM(@strTemp))+ ','
-- OBTAINING THE STARTING POSITION OF COMMA IN THE GIVEN STRING
SET @pos = CHARINDEX(',', @strTemp, 1)
-- CHECK IF THE STRING EXIST FOR US TO SPLIT
IF REPLACE(@strTemp, ',', '') <> ''
BEGIN
WHILE @pos > 0
BEGIN
-- GET THE 1ST INT VALUE TO BE INSERTED
SET @intValue = LTRIM(RTRIM(LEFT(@strTemp, @pos - 1)))
IF @intValue <> ''
BEGIN
INSERT INTO @intTable (Value)
VALUES (CAST(@intValue AS bigint))
END
-- RESETTING THE INPUT STRING BY REMOVING THE INSERTED ONES
SET @strTemp = RIGHT(@strTemp, LEN(@strTemp) - @pos)
-- OBTAINING THE STARTING POSITION OF COMMA IN THE RESETTED NEW STRING
SET @pos = CHARINDEX(',', @strTemp, 1)
END
END
RETURN
END
CREATE Function SplitStringtoInt(@strTemp nVarChar(4000)) Returns @intTable Table([Value] [Int] NOT NULL)
AS
BEGIN
DECLARE @intValue nVarChar(100)
DECLARE @pos int
-- TRIMMING THE BLANK SPACES
SET @strTemp = LTRIM(RTRIM(@strTemp))+ ','
-- OBTAINING THE STARTING POSITION OF COMMA IN THE GIVEN STRING
SET @pos = CHARINDEX(',', @strTemp, 1)
-- CHECK IF THE STRING EXIST FOR US TO SPLIT
IF REPLACE(@strTemp, ',', '') <> ''
BEGIN
WHILE @pos > 0
BEGIN
-- GET THE 1ST INT VALUE TO BE INSERTED
SET @intValue = LTRIM(RTRIM(LEFT(@strTemp, @pos - 1)))
IF @intValue <> ''
BEGIN
INSERT INTO @intTable (Value)
VALUES (CAST(@intValue AS bigint))
END
-- RESETTING THE INPUT STRING BY REMOVING THE INSERTED ONES
SET @strTemp = RIGHT(@strTemp, LEN(@strTemp) - @pos)
-- OBTAINING THE STARTING POSITION OF COMMA IN THE RESETTED NEW STRING
SET @pos = CHARINDEX(',', @strTemp, 1)
END
END
RETURN
END
Usage: SELECT * FROM dbo.
SplitStringtoInt ('12345,87612,988473')
No comments:
Post a Comment