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?
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.
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)
