SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




Install SQL Server 2019
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



Split String using XML - Convert or Split a Delimited String into Rows using SQL XML

SQL developers frequently need to parse or split delimited string values or parameters into a data rows on every database system just as on SQL Server.
Using T-SQL, sql developers or SQL Server database administrators develop different user defined functions UDF to handle string splitting task.
I guess, you have prepared such SQL UDF functions to split and convert comma delimited varchar string values into a table row columns.

With XML enhancements introduced to T-SQL developers and database administrators by SQL Server 2005, programmers are frequently using FOR XML PATH() concatenation method.
Just as concatenation, the opposite task splitting and parsing can also be implemented by using the SQL XML improvements in T-SQL.
Here I will try to demonstrate a sample split sql code which parses comma delimited string inline. This method does not require a user defined function. So this is somehow a little bit more advanced than the regular parsing methods we used to code during our T-SQL splitting tasks.

Of course with SQL Server 2016, database developers can use built-in SQL split string function STRING_SPLIT


Split String using XML T-SQL Example

Here is an SQL example code to split delimited string using XML and XML parsing into table rows.

declare @xml xml, @delimited nvarchar(max), @delimiter nvarchar(10)

set @delimited = N'a,b,c,d,e'
set @delimiter = N','

set @xml = N'<root><r>' + replace((SELECT @delimited FOR XML PATH('') ), @delimiter, '</r><r>') + '</r></root>'

select
  t.value('.','varchar(max)') as [delimited items]
from @xml.nodes('//root/r') as a(t)

The SELECT statement inside REPLACE() SQL function enables developers to escape special characters like "&" used inside the input delimited string. Otherwise SQL engine will throw an XML parsing error similar to:
XML parsing: line 1, character 30, illegal name character

In addition to the delimited string, it is a best practise to escape possible special characters within the delimiter string or delimiter character. So the @xml value assignment code will be as follows:

set @xml = N'<root><r>' + replace((SELECT @delimited as [*] FOR XML PATH('') ), (SELECT @delimiter as [*] FOR XML PATH('')) ,'</r><r>') + '</r></root>'

The syntax used as "as [*]" is for removing the column name. In above syntax removing the "as [*]" part will not make a difference, but in some cases where you use with Cross Apply it will cause a SQL error.

SQL Server database developer can still develop a T-SQL user defined function for string split to use the above code in their future parsing requirements.
In this sql split script with XML, the delimiter character can be defined in a more flexible way.

CREATE FUNCTION Split
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace((SELECT @delimited AS [*] FOR XML PATH('') ), (SELECT @delimiter AS [*] FOR XML PATH('')) ,'</r><r>') + '</r></root>'

  insert into @t(val)
  select
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END
GO

select * from dbo.Split(N'a,b,c,d,e', ',')
select * from dbo.Split('e&e,Art,100%',',')

I hope, you are impressed with the T-SQL trick here. I liked split string using XML parse method much and I have already used string splitting using XML method in our SQL developments on production environments.

SQL split string function using XML Path


A Real Life Problem : An Other Example to Split String using XML

In the below sql problem, I believe sql developers or database administrators frequently experience, I will try to develop a select statement which will split values of a delimited character column value in a table and get the detail information by joining the ID values that we have just splitted.
In this SQL split string example, database table Books has a column Authors which has the id's list as concatenated by comma "," character.
After string split by XML, we will use the each Id splitted to join the BookAuthors table on AuthorId column.

CREATE TABLE BookAuthors (
  AuthorId smallint,
  Author nvarchar(100)
)
GO
CREATE TABLE Books (
  BookId smallint identity(1,1),
  Book nvarchar(100),
  Authors varchar(100)
)
GO
INSERT INTO BookAuthors SELECT 1, N'Paul Turley'
INSERT INTO BookAuthors SELECT 2, N'Dan Wood'
INSERT INTO BookAuthors SELECT 3, N'Itzik Ben-Gan'
INSERT INTO BookAuthors SELECT 4, N'Lubor Kollar'
INSERT INTO BookAuthors SELECT 5, N'Dejan Sarka'

INSERT INTO Books
SELECT N'Beginning T-SQL with Microsoft SQL Server 2005 and 2008', '1,2'
INSERT INTO Books
SELECT N'Inside Microsoft SQL Server 2005: T-SQL Querying', '3,4,5'
GO
--select * from Books
--select * from BookAuthors

If you execute the SQL Select statements on the Query Editor window, the output of the SQL query will be as follows

split comma seperated values in SQL using XML Path function

SELECT
  Books.BookId,
  Books.Book,
  BookAuthors.AuthorId,
  BookAuthors.Author
FROM Books
CROSS APPLY dbo.split(Books.Authors,',') split
INNER JOIN BookAuthors ON BookAuthors.AuthorId = split.val

As seen in above SQL query, concatenated string data in database table columns can be splitted using the sample split string function and Cross Apply. The output of the query is as follows.

split comma seperated string on SQL Server database using split function with cross apply

In this T-SQL split sample code, SQL programmers can see how easy is to use the XML in split string processes.
Cross Apply enables the sql split function which uses XML, to join on string column on the main sql table and return a table which can be joined.

The same split string query can be implemented inline as shown in the below query.

SELECT
  Book.BookId,
  Book.Book,
  BookAuthors.AuthorId,
  BookAuthors.Author
FROM (
  SELECT
    BookId,
    Book,
    CAST('<r>' + REPLACE( (select Authors as [*] for xml path('')), ',', '</r><r>') + '</r>' AS XML) Authors
  FROM Books
) Book
CROSS APPLY (
  SELECT
    Authors.AuthorId.value('.', 'varchar(10)') AuthorId
  FROM Book.Authors.nodes('r') AS Authors(AuthorId)
) Split
INNER JOIN BookAuthors ON BookAuthors.AuthorId = Split.AuthorId

If SQL Server database programmers compare the output of the two above queries where in the first one a SQL split string function is used and in the other an inline split string SQL is used, it is seen that both outputs are exactly the same.

SQL code to split string inline on SQL Server database sample

But if we examine the Include Actual Execution Plan for both string split methods on SQL Server, executing a string split function using XML or directly inline splitting string using XML, I see that using a user-defined function UDF which splits string using XML is better to use by comparing each Query Cost.

I will also suggest database developers to check SQL Server Recursive T-SQL Split Function where SQL recursive CTE expression is used for solving string split problems as an alternative solution.

On your SQL Server database platform, if you are already using CLR functions or procedures, then as a database developer you can also use SQL CLR Function to split string






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums








SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function


Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems