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

Code Alone

8y28.8k9100Article

SQL ServerSplit A Delimiter-Separated StringTabular Function

9 Comments

Join the conversation! Your thoughts help the community grow.

Hadshana Kamalanathan8y ago
Thanks for sharing...
Jean-Patrick8y ago
If you use SQL Server 2016, you can simplify your SplitPra function by using the built-in string_split function => CREATE FUNCTION [dbo].[SplitPra] (@Value VARCHAR(MAX), @delimiter CHAR) RETURNS TABLE AS RETURN SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 1)) Position, value FROM STRING_SPLIT(@Value, @delimiter);
- Code Alone8y ago
  Many Thanks for your information. But in below 2016 this built in function is not available. So we can use this user defined function.
Shweta688y ago
Thanks for sharing. Very useful content.
- Code Alone8y ago
  Thank you shwetha
Nikki8y ago
Yesterday I asked this type of issue on forum, And today you wrote complete descriptive article. Very Nice and useful thing you shared. Thanks again sir.
- Code Alone8y ago
  Welcome Nikita Gupta. I will share more worthy contents in upcoming articles.
Sanwar Ranwa8y ago
Very helpful article sir thanks for sharing
- Code Alone8y ago
  Thank you so much Sanwar Ranwa for your great words and these makes my writing better. And please share this with the new coders. I will share more worth contents in upcoming article series.

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,

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.
We need to split the string containing words separated (delimited) by the comma into table values with their exact position.
We want to reduce the load on the DB server.
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', ',')

Calling the Function By Query

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.