SQL Server - Function to Get individual values from Comma Separated Values (CSVs)

1. CSV - String

CREATE FUNCTION[dbo].[CsvToString] ( @Array VARCHAR(1000))
RETURNS @StrTable TABLE
(StrValue VARCHAR(1000))
AS
BEGIN
DECLARE @separator CHAR(1)
SET @separator = ','
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(1000)
SET @array = @array + ','
WHILE patindex('%,%' , @array) <> 0
BEGIN
SELECT @separator_position = patindex('%,%' , @array)
SELECT @array_value = left(@array, @separator_position - 1)
INSERT @StrTable
VALUES (Cast(@array_value as VARCHAR(1000)))
SELECT @array = stuff(@array, 1, @separator_position, '')
END
RETURN
END

2. CSV - Int

CREATE Function [dbo].[CsvToInt] ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(1000)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @IntTable
Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
return
end

3. SSV(Semicolon Separated Values) - Int

ALTER FUNCTION[dbo].[SemisvToString] ( @Array VARCHAR(1000))
RETURNS @StrTable TABLE
(StrValue VARCHAR(1000))
AS
BEGIN
DECLARE @separator CHAR(1)
SET @separator = ';'
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(1000)
SET @array = @array + ';'
WHILE patindex('%;%' , @array) <> 0
BEGIN
SELECT @separator_position = patindex('%;%' , @array)
SELECT @array_value = left(@array, @separator_position - 1)
INSERT @StrTable
VALUES (Cast(@array_value as VARCHAR(1000)))
SELECT @array = stuff(@array, 1, @separator_position, '')
END
RETURN
END

234 unique visitors