While Loop and Split function in T-SQL

Question

I have a use case where my SQL Script is having a variable with comma separated values.

whitelistURL = abc.com,xyz.com

I have to run insert command on all the values of the variable. Something like split with comma and run insert on abc.com and insert on xyz.com

Can someone please suggest the best way to do it. I am new to SQL.

I am using SQL Server 2014.

Where are you getting the `whitelistURL` from? If possible, you should avoid using delimited strings and work with a table valued parameter instead. — Zohar Peled, Mar 07 '18 at 07:23

score 2 · Answer 1 · answered Mar 07 '18 at 06:26

Try this:

declare @whitelistURL varchar(100) = 'abc.com,xyz.com,xyz.com,'
--set @whitelistURL = @whitelistURL + ','
;with cte as(
select charindex(',', @whitelistURL) [n], SUBSTRING(@whitelistURL, 1, 
charindex(',', @whitelistURL) - 1) [url]
union all
select charindex(',', @whitelistURL, n + 1), SUBSTRING(@whitelistURL, n + 1, charindex(',', @whitelistURL, n + 1) - n - 1) from cte
where charindex(',', @whitelistURL, n + 1) > 0
)

select [url] from cte

Only thing to note is that processed string should have delimeter (comma) also at the end, but if it doesn't, just append it, as I did in commented line.

Then insert statement becomes really easy:

insert into MY_TABLE (url) values
select url from cte

score 1 · Answer 2 · answered Mar 17 '18 at 17:00

You can use a custom split function. There are a lot of implementations of these functions on the web, here is an example:

CREATE FUNCTION [dbo].[fn_Split] (@Text nvarchar(MAX), @Delim char(1))
    RETURNS @Values TABLE (Value nvarchar(MAX))
AS
    BEGIN
        SET @Text = RTRIM(LTRIM(@Text))
        DECLARE @chrind int
        DECLARE @Piece nvarchar(100)
        SELECT @chrind = 1
        WHILE @chrind > 0
            BEGIN
                SELECT @chrind = CHARINDEX(@Delim, @Text)

                IF @chrind > 0
                    SELECT @Piece = RTRIM(LTRIM(LEFT(@Text, @chrind - 1)))
                ELSE
                    SELECT @Piece = RTRIM(LTRIM(@Text))
                INSERT @Values(Value) VALUES(CAST(@Piece as varchar(100)))
                SELECT @Text = RIGHT(@Text, LEN(@Text) - @chrind)
                IF LEN(@Text) = 0 BREAK
            END
        RETURN
    END

This function takes two parameters:

the string to split (in your case 'abc.com,xyz.com,xyz.com,')
the separator (in your case ',')

Now that you have your split function you can use it like this:

select [Value] from [dbo].[fn_Split]('abc.com,xyz.com',',')

This is the output of this command:

You can now combine this with an insert statement:

insert into YOUR_TABLE_NAME(YOUR_COLUMN_NAME) 
select [Value] from [dbo].[fn_Split]('abc.com,xyz.com',',')

While Loop and Split function in T-SQL

2 Answers2