Tim - 5 months ago 20

SQL Question

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', ',');`

`SELECT * FROM dbo.SplitStrings_CTE('a b c', ' ');`

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]-@ld)

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;
```