Simple way of converting elements in a comma delimited string into row values in a SQL table

13 August 2019 Admin DataBases

INTRODUCTION

In my previous article Simple way to get comma delimited values from a field by using SQL I showed how to get a comma delimited string from a field in a SQL table. But, there are other times you want to get the same but the other way around, that is, converting each element in a comma (or another character) delimited string variable into a row value in a table. How to get this, then?

BACKGROUND

Before going ahead, if you are not familiar with XML type data in SQL Server, please take a look at the following link. It provides an overview about how to deal with XML types in SQL Server that will result very useful to understand the approach we are going to employ in this article.

https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-data-type-and-columns-sql-server

IMPLEMENTATION

Let's get straight to the point. First of all, we will create a new SQL function accepting two input parameters. The former will be used to pick up the delimited string, and the latter will set the character employed as separator (as from this, "@separator"). This way, our function will be able to process any type of single character delimited string, not only "comma" delimited string. The default value for this second parameter will be a "comma".

Having said this, we will make use of an XML variable type to store data from the input "@separator" delimited string after being smartly converted into a proper format. Then, we will employ the capabilities provided for XML type to obtain desired values from nodes. And that's all !!

Here is the function:

CREATE FUNCTION [dbo].[GetTableFromDelimitedString]
(
    @inputString varchar(max),
    @separator nvarchar(1) = ','
)  
RETURNS  @TableName TABLE(ColumnName nvarchar(max)) 
AS  
BEGIN  

      SET @separator = isnull(@separator,',') 
      
      DECLARE @x XML  
      SELECT @x = CAST('<root><row>'+ REPLACE(@inputString,@Separator,'</row><row>') + '</row></root>' AS XML)

      INSERT INTO @TableName(ColumnName)
      SELECT x.t.value('.', 'nvarchar(max)') 
      FROM @x.nodes('/root/row') AS x(t) 

    RETURN 
END
GO

Here is the way of calling it:

select * from [GetTableFromDelimitedString]('a;b;c;d',';') 
--or 
select * from [GetTableFromDelimitedString]('a,b,c,d',',')

And finally, here are the results:

ColumnName
a
b
c
d

EXPLANATION

Firstly, we declare and use a XML variable to store the delimited string, after being converted properly:

DECLARE @x XML  
SELECT @x = CAST('<root><row>'+ REPLACE(@inputString,@Separator,'</row><row>') + '</row></root>' AS XML)

If you set a value for the @inputString and @separator variables and select the value of @x, you will get something like this (let's suppose that @inputString = 'a,b,c,d' and @separator = ','):

<root>
  <row>a</row>
  <row>b</row>
  <row>c</row>
  <row>d</row>
</root>

Secondly, once we have stored the previous XML variable, we can query information. To do this, we are using the "nodes" method of the "@x" XML variable to return all the "row" elements. Notice the query string used in the method ('/root/row') to select nodes.

If we execute the following code that contains a new select query over the previous one:

DECLARE @x XML, @Separator nvarchar(1)
SET @Separator = ','   
SELECT @x = CAST('<root><row>'+ REPLACE('a,b,c,d',@Separator,'</row><row>') + '</row></root>' AS XML)
SELECT x.t.query('.') AS Val  
FROM @x.nodes('/root/row') AS x(t)

We will obtain this result:

<row>a</row>
<row>b</row>
<row>c</row>
<row>d</row>

This is because we have used the "query" method.

But, we just want the "values", not all the nodes. Well, the fix is very simple, the only change to do is to substitute the "query" method by the "value" one. This will extract the "value" for each node, and perform a cast to convert the value in a variable of the indicated type ("nvarchar(max)" in this case)

DECLARE @x XML, @Separator nvarchar(1)
SET @Separator = ','   
SELECT @x = CAST('<root><row>'+ REPLACE('a,b,c,d',@Separator,'</row><row>') + '</row></root>' AS XML)
SELECT x.t.value('.','nvarchar(max)') AS Val  
FROM @x.nodes('/root/row') AS x(t)

Any other way of achiving this? Please, add a comment below smiley

Tags :

Add comment