My Technical Notes

Monday, 11 March 2013

Splitting a Comma Separated String in a Stored Procedure

In SQL, we might have a need to send a string of values to a stored procedure. We can do this neatly using XML, but we can also do this using a comma separated list of values. One technical difficulty with this solution is that, in the SQL Server, we need to split the string into its constituent parts.

A method which comes to mind is to do a replacement on the comma character and turn the string into an XML document, after which we can select the nodes:


DECLARE @testInput nvarchar(max) ; SET @testInput = 'first,second,third';
DECLARE @xml XML ; SET @xml = CONVERT(XML,'<vals><val>' + REPLACE(@testInput,',', '</val><val>') + '</val></vals>')

SELECT 
    v.value('.', 'nvarchar(200)')
FROM @xml.nodes('/vals/val') as Vals(v)

However, this does not work on strings which have XML entities in them. For example, the string:


Barnes & Noble,Amazon

Will not work, as there is the entity & in the string. Therefore we need to encode the input string before we can turn it into a document:


DECLARE @str NVARCHAR(100) ; SET @str = 'Barnes & Nobles,Amazon'
DECLARE @str2 NVARCHAR(MAX) ; SET @str2 = (SELECT CONVERT(NVARCHAR(MAX), (SELECT @str FOR XML PATH(''))))

DECLARE @xml XML ; SET @xml = CONVERT(XML,'<vals><val>' + REPLACE(@str2  ,',', '</val><val>') + '</val></vals>')

SELECT 
    v.value('.', 'nvarchar(200)')
FROM @xml.nodes('/vals/val') as Vals(v)

The above would look very unsightly if we were to include it in every stored procedure. Therefore, we would turn it into a TABLE-VALUED function, after which we can call it multiple times from different stored procedures:


IF OBJECT_ID(N'dbo.splitCommaSeparatedString', N'TF') IS NOT NULL
    DROP FUNCTION dbo.splitCommaSeparatedString;
GO

CREATE FUNCTION dbo.splitCommaSeparatedString(@inputString NVARCHAR(MAX))
RETURNS @strings TABLE(string NVARCHAR(200) NOT NULL)
AS
BEGIN
    DECLARE @encodedString NVARCHAR(MAX) ;
    SET @encodedString = (SELECT CONVERT(NVARCHAR(MAX), (SELECT @inputString FOR XML PATH(''))))
    
    DECLARE @xml XML ; 
    SET @xml = CONVERT(XML,'<vs><v>' + REPLACE(@encodedString,',', '</v><v>') + '</v></vs>')
    
    INSERT INTO @strings
    SELECT X.x.value('.', 'nvarchar(200)')
    FROM @xml.nodes('/vs/v') as X(x)

    RETURN
END

To call it we treat it almost like a table with a parameter:


SELECT * FROM dbo.splitCommaSeparatedString('Barnes & Noble,Amazon,Waterstones')

The above code returns the following (correct) output:


string
----------------
Barnes & Noble
Amazon
Waterstones

No comments: