Tim Tim - 1 year ago 53
SQL Question

Why does this SQL to split strings work with a comma delimited string but not a space delimited string?

Using the SQL function provided in the Common Table Expression section of this blog post (copied below for convenience), I can split a comma separated string into a table containing each section of the string.

SELECT * FROM dbo.SplitStrings_CTE('a,b,c', ',');
runs in a fraction of a second and returns the expected results. However, if I change the delimiter from a comma to a space,
SELECT * FROM dbo.SplitStrings_CTE('a b c', ' ');
never seems to finish executing. I've been trying to understand how the function works, but this aspect of it has me stumped. Why does it not seem able to handle using a space as a delimiter?

The function is:

CREATE FUNCTION dbo.SplitStrings_CTE
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS @Items TABLE (Item NVARCHAR(4000))
WITH SCHEMABINDING
AS
BEGIN
DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);

WITH a AS
(
SELECT
[start] = 1,
[end] = COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, 1), 0), @ll),
[value] = SUBSTRING(@List, 1,
COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, 1), 0), @ll) - 1)
UNION ALL
SELECT
[start] = CONVERT(INT, [end]) + @ld,
[end] = COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, [end] + @ld), 0), @ll),
[value] = SUBSTRING(@List, [end] + @ld,
COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, [end] + @ld), 0), @ll)-[end][email protected])
FROM a
WHERE [end] < @ll
)
INSERT @Items SELECT [value]
FROM a
WHERE LEN([value]) > 0
OPTION (MAXRECURSION 0);

RETURN;
END
GO

Answer Source

Here is why:

@ld INT = LEN(@Delimiter);

In Sql Server, LEN ignores trailing spaces - and when there is only one space, it's LEN will return 0:

SELECT  LEN(',') As [LEN(',')], 
        LEN(N' ') As [LEN(N' ')], 
        LEN(N' 1') As [LEN(N' 1')], 
        LEN(N'1 ') As [LEN(N'1 ')]

Returns:

LEN(',')    LEN(N' ')   LEN(N' 1')  LEN(N'1 ')
1           0           2           1

To fix this, you can use DATALENGTH. However, since this is NVARCHAR, the DATALENGTH function will return the number of chars * 2, so you need to divide the result by 2:

@ld INT = DATALENGTH(@Delimiter)/2;

See a live demo on rextester.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download