Splitting text into lines in Microsoft SQL Server

Question

How to split text into lines (or words) in Microsoft SQL Server?

I searched and found the new STRING_SPLIT, but found it actually isn't working as I expected after trying it:

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
SELECT STRING_SPLIT(@text, CHAR(13));
-- 'STRING_SPLIT' is not a recognized built-in function name.

I.e., the new STRING_SPLIT is a table instead of a function.

So, how to split text into lines in Microsoft SQL Server?

What version of SQL Server are you using? [Have you read this question](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) ? — Tim Biegeleisen, Aug 20 '18 at 13:57
What do you mean by "it actually isn't working as I expected"? What do you expect it to do? You have more than enough rep around here to know that such a vague question is pretty difficult to help with. — Sean Lange, Aug 20 '18 at 14:17

score 3 · Accepted Answer · answered Aug 20 '18 at 14:04

You can create a functtion ike this:

CREATE FUNCTION dbo.SPLIT(
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
        AS
        BEGIN
          DECLARE @xml XML
          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

          INSERT INTO @t(val)
          SELECT  r.value('.','varchar(MAX)') as item
          FROM  @xml.nodes('/t') as records(r)
          RETURN
        END

and then use like this:

SELECT * FROM [dbo].[SPLIT] ('Lorem ipsum dolor sit amet.', ' ')

the result is:

1   Lorem
2   ipsum
3   dolor
4   sit
5   amet.

score 0 · Answer 2 · answered Aug 20 '18 at 14:30

You can do this with xml as below

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='Lorem ipsum dolor sit amet.'
SET @delimiter =' '
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

Splitting text into lines in Microsoft SQL Server

2 Answers2