Tabular Function To Split A Delimiter-Separated String To Rows With Their Position

Introduction

Today we will learn how to split and convert a delimited string to a table  in the SQL Server. The string contains words separated (delimited) by commas that will be split into Table values. Sometimes we face the below issues,

1. There are many cases where we need to pass the input parameter to the stored procedure as the delimited-separated string to avoid multiple DB calls.
2. We need to split the string containing words separated (delimited) by the comma into table values with their exact position.
3. We want to reduce the load on the DB server.
4. Sometimes we have 2 delimited strings and join them by position.

To resolve the above issues we need a table-valued function, which accepts a string and delimiter and returns the split values into rows with their position.

Issue

I have a string which contains employee IDs separated by commas,

@str='21,28,13,19,209.301,146,151,203,450,680, 98'

We need the below result,

Implementation

In this case, I generate an auto incremented field to save the respected position of that word, and separate the words into rows. Actually I need a to return a table with the below definition.

[ID] TINYINT IDENTITY(1,1),
[Value] NVARCHAR(128)

SQL Server User Defined Function,

CREATE FUNCTION [dbo].[SplitPra] (@Value VARCHAR(MAX), @delimiter CHAR)
RETURNS @DataResult TABLE([Position] TINYINT IDENTITY(1,1),[Value] NVARCHAR(128))
AS
BEGIN
DECLARE @XML xml = N'<r><![CDATA[' + REPLACE(@Value, @delimiter, ']]></r><r><![CDATA[') + ']]></r>'
INSERT INTO @DataResult ([Value])
SELECT RTRIM(LTRIM(T.c.value('.', 'NVARCHAR(128)')))
FROM @xml.nodes('//r') T(c)
RETURN
END

Here @Value is a string which contains many words separated by a delimiter.

@delimiter is a character which is used to separate the words in a string.

Calling the Function By Query

SELECT * FROM SplitPra('21,281,13,19,209,301,146,151,203,450,680, 98',',')

 

 

In the above calling, I used numbers only. We can put words there like,

SELECT * FROM SplitPra('United States, China, Japan, Germany,United Kingdom, France, India, Italy, Brazil, Canada', ',')

 

Joining Two delimited strings result in one table

We have two strings. First string contains countries, separated by a comma.

@countries ='United States, China, Japan, Germany, United Kingdom, France, India, Italy, Brazil, Canada,'

And the second string contains their GDP growth percent in year 2015 over year 2012.

@growth='11.08%,28.42%,-30.78%,-5.19%,8.28%,-9.69%,13.65%,-12.45%,-27.88%,-15.02%'

Use the below query to join then,

DECLARE @countries NVARCHAR(MAX), @growth NVARCHAR(MAX)
SET @countries='United States,China,Japan,Germany,United Kingdom, France, India, Italy, Brazil ,Canada'
SET @growth= '11.08%,28.42%,-30.78%,-5.19%,8.28%,-9.69%,13.65%,-12.45%,-27.88%,-15.02%'
SELECT countries.Position,countries.Value AS 'Country', growth.Value 'Growth'
FROM SplitPra(@countries,',') countries ,SplitPra(@growth,',') growth
WHERE countries.position=growth.position

 

 

Summary

You can see the result was generated as above, and you can change the delimiter character. If you have some alternate way to achieve this kind of requirement then please let me know, or if you have some query then please leave your comments.