#0279 – SQL Server – How to use the REPLACE() function to insert values supplied as a Comma-separated list into a table


Often, the correct answer to any SQL Server problem is just two words – “It depends!” And the fact is that the more I work with SQL Server, the importance of these two words cements itself into the foundations of my thinking.


Let’s take an example which I came across recently, and which prompted this post.


The Challenge


For one of the ad-hoc training assignments that I was supervising, the design was such that there would be a comma-separated list as input to one of the stored procedures. These values had to be inserted as records into a table. The application/script was to be run on SQL Server 2008 R2 and above only.


If we talk about implementation, there are a couple of different ways in which it can be done with approaches ranging from using a simple WHILE loop to using PATINDEX. While all participants came up with these, I decided to spice things up a bit. The challenge I presented to them was:



Generate the statement to insert values from a comma-separated string to a table in a single T-SQL statement.


The Answer


The answer to this problem is in thinking simple. Given that the script needs to run only on SQL Server 2008 R2 and above, we can convert the comma-separated string into row constructors for use with the VALUES clause. This can be done in a single T-SQL statement by using the REPLACE function as shown below:

USE tempdb ;
GO
BEGIN
SET NOCOUNT ON ;

–Safety Check
IF OBJECT_ID(N’#productNames’, N’U’) IS NOT NULL
BEGIN
DROP TABLE #productNames ;
END

CREATE TABLE #productNames ( Product VARCHAR(25) ) ;

–Input Parameter
DECLARE @parameterNames NVARCHAR(MAX) = N’Microsoft,SQL Server,Windows,Office,Sharepoint,BizTalk’ ;

–Query Statement
DECLARE @stmnt NVARCHAR(1000) ;

–Build the query
–Key is to use the REPLACE function here
SELECT @stmnt = N’INSERT INTO #productNames (Product) VALUES ‘ + N'(”’
+ REPLACE(@parameterNames, ‘,’, ”’),(”’) + N”’)’ ;

–Check the query
SELECT @stmnt AS QueryStatement ;

–Execute the query
EXEC sp_executesql @stmnt ;

–Check the query result
SELECT pn.Product
FROM #productNames AS pn ;

–Cleanup
IF OBJECT_ID(N’#productNames’, N’U’) IS NOT NULL
BEGIN
DROP TABLE #productNames ;
END
END
GO

/***********************************
RESULT
(formatted for sake of readability)
***********************************/
/*
QueryStatement
————————————
INSERT INTO #productNames (Product)
VALUES (‘Microsoft’),
(‘SQL Server’),
(‘Windows’),
(‘Office’),
(‘Sharepoint’),
(‘BizTalk’)

Product
———–
Microsoft
SQL Server
Windows
Office
Sharepoint
BizTalk
*/


Needless to say this approach comes with all the restrictions of row constructor, the prime one being that the number of records is restricted to 1000. The script above can easily be modified to take this into account as well.


Conclusion


In conclusion, all I would say is there are many different ways to approach a problem in SQL Server. The correct approach is one that works for the situation and problem at hand.


Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

7 thoughts on “#0279 – SQL Server – How to use the REPLACE() function to insert values supplied as a Comma-separated list into a table

  1. dishdy

    Nice variant for solving the classic unpivot (horizontal to vertical) problem.
    But this is impossible to use in functions.
    Thus I will stick to this classic solution:

    select split.a.value(‘.’,’varchar(1000)’) as item
    from (select cast(”+replace(‘Microsoft,SQL Server,Windows,Office,Sharepoint,BizTalk’,’,’,”)+” as xml) csl) t
    cross apply csl.nodes(‘/M’) split(a)

    Like

    Reply
  2. marc_jellinek@hotmail.com

    What an amazing security hole, ripe for a SQL Injection attack.

    See what happens when you change @parameterNames to this:

    DECLARE @parameterNames NVARCHAR(MAX) = N’Microsoft,SQL Server,Windows”);CREATE LOGIN EvilPerson WITH PASSWORD=”xx*yy*zz*11*22*33”;ALTER SERVER ROLE sysadmin ADD MEMBER EvilPerson–,Office,Sharepoint,BizTalk’ ;

    Congratulations! You now have a brand new sysadmin on the server, who can do anything they like.

    Do not EVER put code this this into production.

    Like

    Reply
  3. marc_jellinek@hotmail.com

    Instead of using dynamic SQL, which is vulnerable to a SQL Injection attack, try this:

    USE tempdb ;
    GO
    BEGIN
    SET NOCOUNT ON ;

    –Safety Check
    IF OBJECT_ID(N’#productNames’, N’U’) IS NOT NULL
    BEGIN
    DROP TABLE #productNames ;
    END

    CREATE TABLE #productNames ( Product VARCHAR(25) ) ;

    –Input Parameter
    DECLARE @parameterNames NVARCHAR(MAX) = N’Microsoft,SQL Server,Windows,Office,Sharepoint,BizTalk’ ;

    WITH — start generating tally numbers
    CTE_Tally_1 AS ( SELECT CAST(1 as [int]) as [value] UNION ALL SELECT CAST(1 as [int]) as [value] ),
    CTE_Tally_2 AS ( SELECT [a].[value] FROM [CTE_Tally_1] [a], [CTE_Tally_1] [b], [CTE_Tally_1] [c] ),
    — present tally numbers
    CTE_Tally_Numbers AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as [value] FROM [CTE_Tally_2] [a], [CTE_Tally_2] [b], [CTE_Tally_2] [c], [CTE_Tally_2] [d], [CTE_Tally_2] [e] ),
    — break string into individual characters, present as a table
    CTE_Characters AS ( SELECT [value],
    SUBSTRING(@parameterNames, [value], 1) as [character]
    FROM CTE_Tally_Numbers
    WHERE SUBSTRING(@parameterNames, [value], 1) N”
    ),
    — parse out the breaks ‘,’
    CTE_Breaks AS ( SELECT ROW_NUMBER() OVER (ORDER BY [value]) as [num],
    [value]
    FROM CTE_Characters
    WHERE [character] = ‘,’
    ),
    — calculate the start and end of each substring
    CTE_Start_End AS ( SELECT ISNULL([start].[value], 0) + 1 as [start],
    ISNULL([end].[value], 9999999) – 1 as [end]
    FROM [CTE_Breaks] [end] FULL OUTER JOIN [CTE_Breaks] [start] ON [start].[num] = [end].[num] – 1
    )
    INSERT INTO #productNames (Product)
    — present back each value as a row in a table
    SELECT SUBSTRING(@parameterNames, [start], [end]-[start] + 1)
    FROM CTE_Start_End
    ORDER BY [start]

    –Check the query result
    SELECT pn.Product
    FROM #productNames AS pn ;

    –Cleanup
    IF OBJECT_ID(N’#productNames’, N’U’) IS NOT NULL
    BEGIN
    DROP TABLE #productNames ;
    END
    END
    GO

    /***********************************
    RESULT
    ***********************************/
    /*
    Product
    ———–
    Microsoft
    SQL Server
    Windows
    Office
    Sharepoint
    BizTalk
    */

    Like

    Reply
  4. Nakul Vachhrajani

    @Marc: Completely agree with you. The method shown is absolutely not “production-grade”, but just wanted to present to the attendees a different way to look at things.

    Your method of using tally numbers/table is a good one! Just another proof that the goal can be achieved in T-SQL via multiple different approaches.

    Thank-you for sharing the script and providing your comments.

    Like

    Reply
  5. marc_jellinek@hotmail.com

    Nakul, you present this as the “correct” answer in the context of an “ad-hoc training assignment” that you were supervising.

    It’s only after you are called out in a comment that you acknowledge that your solution is not “production grade”.

    How is something correct, but not production grade?

    The ONLY code that should ever be posted as a “correct” solution is one that is “production grade”.

    Your “correct” solution is open to a SQL injection attack, putting the entire business at risk.

    Like

    Reply
  6. dishdy

    Marc,
    This injection will only work if the user running the code has the necessary privileges.via a server role.
    A normal user running this injection will simply generate an error.
    I tend to give minimal privileges to users running application code.
    BTW, your code returns no rows when the parameter value does not contain at least one comma.
    In fact, my code is getting posted incorrectly and thus produces incorrect results as well.
    Will have a go at fixing it.

    Like

    Reply
  7. marc_jellinek@hotmail.com

    dishdy, the ability to provision a new user and make them a sysadmin is an extreme case.

    The injected string could just as easily drop tables, delete rows, insert bad data or just run a tight loop for a Denial of Service attack.

    You were right about not returning any rows if there was no comma in the parameter. Here’s the update.

    USE tempdb ;
    GO
    BEGIN
    SET NOCOUNT ON ;

    –Safety Check
    IF OBJECT_ID(N’#productNames’, N’U’) IS NOT NULL
    BEGIN
    DROP TABLE #productNames ;
    END

    CREATE TABLE #productNames ( Product VARCHAR(25) ) ;

    –Input Parameter
    DECLARE @parameterNames NVARCHAR(MAX) = N’Microsoft,SQL Server,Windows,Office,Sharepoint,BizTalk’ ;

    WITH — start generating tally numbers
    CTE_Tally_1 AS ( SELECT CAST(1 as [int]) as [value] UNION ALL SELECT CAST(1 as [int]) as [value] ),
    CTE_Tally_2 AS ( SELECT [a].[value] FROM [CTE_Tally_1] [a], [CTE_Tally_1] [b], [CTE_Tally_1] [c] ),
    — present tally numbers
    CTE_Tally_Numbers AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as [value] FROM [CTE_Tally_2] [a], [CTE_Tally_2] [b], [CTE_Tally_2] [c], [CTE_Tally_2] [d], [CTE_Tally_2] [e] ),
    — break string into individual characters, present as a table
    CTE_Characters AS ( SELECT [value],
    SUBSTRING(@parameterNames, [value], 1) as [character]
    FROM CTE_Tally_Numbers
    WHERE SUBSTRING(@parameterNames, [value], 1) N”
    ),
    — parse out the breaks ‘,’
    CTE_Breaks AS ( SELECT ROW_NUMBER() OVER (ORDER BY [value]) as [num],
    [value]
    FROM CTE_Characters
    WHERE [character] = ‘,’
    ),
    — calculate the start and end of each substring
    CTE_Start_End AS ( SELECT ISNULL([start].[value], 0) + 1 as [start],
    ISNULL([end].[value], 9999999) – 1 as [end]
    FROM [CTE_Breaks] [end] FULL OUTER JOIN [CTE_Breaks] [start] ON [start].[num] = [end].[num] – 1
    )
    –INSERT INTO #productNames (Product)
    — present back each value as a row in a table
    SELECT SUBSTRING(@parameterNames, [start], [end]-[start] + 1)
    FROM CTE_Start_End
    WHERE EXISTS (SELECT * FROM CTE_Start_End)
    UNION ALL
    SELECT TOP 1
    @parameterNames
    FROM CTE_Tally_1
    WHERE NOT EXISTS (SELECT * FROM CTE_Start_End)

    –Check the query result
    SELECT pn.Product
    FROM #productNames AS pn ;

    –Cleanup
    IF OBJECT_ID(N’#productNames’, N’U’) IS NOT NULL
    BEGIN
    DROP TABLE #productNames ;
    END
    END
    GO

    /***********************************
    RESULT
    ***********************************/
    /*
    Product
    ———–
    Microsoft

    SQL Server
    Windows
    Office
    Sharepoint
    BizTalk
    */

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s