From time to time I'm facing with trouble in SQL how to convert comma separated string into table.
For instance, you may have set of comma delimited parameters that you want to use in select:
"'US','UA','RU','DE'"
You want to use this set of country codes to select clients.
Without converting the comma separated string into table you will have to use dynamic SQL:
exec 'Select * from clients where Country in (' + @countryList + ')'
This solution is not very good because if you have request bigger then one line, its will be difficult to trace it in case of errors.
There is a small function that converts comma separated string into table:
CREATE FUNCTION [dbo].[fn_CommaSeparatedStringToTable]
(
@CommaSeparatedValues VARCHAR(MAX),
@IncludeEmptyStrings CHAR(1)
)
RETURNS @Item TABLE
(
RowId int IDENTITY(1, 1) NOT NULL,
Value VARCHAR(200)
)
AS
BEGIN
DECLARE @IndefOfComma int,@Value VARCHAR(200),@StartPos bigint,@EndPos bigint,@LengthOfString int, @ReachedEnd Char(1)
SET @StartPos=1
SET @EndPos=0
SET @LengthOfString=LEN(@CommaSeparatedValues)
SET @ReachedEnd='N'
WHILE @ReachedEnd<>'Y'
BEGIN
SET @EndPos=CHARINDEX(',',@CommaSeparatedValues,@StartPos)
IF @EndPos>0
BEGIN
SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos,@EndPos-@StartPos)
SET @StartPos=@EndPos+1
END
ELSE
BEGIN
SEt @ReachedEnd='Y'
SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos,@LengthOfString-(@StartPos-1))
END
IF(@Value<>'' OR @IncludeEmptyStrings='Y')
INSERT INTO @Item(Value) VALUES(@Value)
END
RETURN
END
After using this function, you can use reqular sql queries:
Select
*
from
clients c
inner join dbo.fn_CommaSeparatedStringToTable(@countryList, 'N') t on t.Value = c.Country
As you may see, this way is much more useful.
