Function to Split Comma separated string to Integer

There are cases where we need to pass the input parameter to the stored procedure as a comma-separated string in order to avoid multiple db calls from happening. This helps in reducing the db calls thereby reducing the load on the db server.

The below function will split the given comma-separated string into integers and process the results.

[sql]
IF EXISTS( SELECT [name]
FROM [sys].[objects]
WHERE [name] = ‘fnSplitBigInt’
AND [type] = ‘TF’ )
DROP FUNCTION [dbo].[fnSplitBigInt];
GO
CREATE FUNCTION [dbo].[fnSplitBigInt](
— Add the parameters for the function here
@input NVARCHAR(4000))
RETURNS @retBigint TABLE( [Value] [BIGINT] NOT NULL )
AS
BEGIN
DECLARE @bigint NVARCHAR(100);
DECLARE @pos [INT];
SET @input = LTRIM(RTRIM(@input)) + ‘,’; — TRIMMING THE BLANK SPACES
SET @pos = CHARINDEX(‘,’, @input, 1); — OBTAINING THE STARTING POSITION OF COMMA IN THE GIVEN STRING
IF REPLACE(@input, ‘,’, ”) <> ” — CHECK IF THE STRING EXIST FOR US TO SPLIT
BEGIN
WHILE @pos > 0
BEGIN
SET @bigint = LTRIM(RTRIM(LEFT(@input, @pos – 1))); — GET THE 1ST INT VALUE TO BE INSERTED
IF @bigint <> ”
BEGIN
INSERT INTO @retBigint
( Value )
VALUES( CAST(@bigint AS [BIGINT]));
END;
SET @input = RIGHT(@input, LEN(@input) – @pos); — RESETTING THE INPUT STRING BY REMOVING THE INSERTED ONES
SET @pos = CHARINDEX(‘,’, @input, 1); — OBTAINING THE STARTING POSITION OF COMMA IN THE RESETTED NEW STRING
END;
END;
RETURN;
END;
[/sql]

The following is an sample output result set of that function

[sql]
SELECT *
FROM [dbo].[fnSplitbigint]( ‘12345,87612,988473’ );[/sql]

integer_split_function

Method2

The 2nd method is a much more simpler and optimal way of splitting the comma separated string

[sql]
DECLARE @xml AS XML,
@str AS VARCHAR(100),
@delimiter AS VARCHAR(10);
SET @str = ‘12345,87612,988473’;
SET @delimiter = ‘,’;
SET @xml = CAST(‘<X>’ + REPLACE(@str, @delimiter, ‘</X><X>’) + ‘</X>’ AS XML);
SELECT [N].value( ‘.’, ‘varchar(50)’ ) AS value
FROM @xml.nodes( ‘X’ ) AS [T]( [N] ); [/sql]


Posted

in

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *