SQL Functions

Our function today is demonstrated below. It uses a Sequence (Or Tally) table containing the numbers 0 thru [some number that is big enough for your requirements].

I am using two common table expressions. The first CTE, Pointers, locates the first position of the Delimiter string.

The second CTE, Segments, uses three queries to find the start position of the data desired, and the length of the value until the next pointer. The first query starts at position 1, and finds the position of the first pointer. Because I create a sequential row number for each record in the Pointers CTE, I can filter on PointerOrder = 1, to only get the first row.

Next I add all of the records except the first and last by joining Pointers to itself. The first instance adds 1 to PointerOrder. It then joins on the second instance of Pointer on PonterOrder. This results in one row containing the data from the current row and the next row. Because there is no row for the first position left of the first Delimiter, it can’t be found to join to the second row. That was why we did the first query above. So, we are starting at row 2 and going to the second from last row, because there is no Delimiter at the end.

Finally we simply add in the last row. It starts after the Pointer found in the last row of Pointers + the length of the Delimiter. The length is calculated as the length of the string being searched, @String – the length of the Delimiter).

With my two CTEs in place I can now return the different substrings found within my variable @String, thus splitting on a variable length Delimiter. I provide an example at the end, demonstrating this function using a string of numbers delimited by a comma and a space ‘, ‘.

Tomorrow I’ll show you how to take that split string and convert it into a binary number, and then HEX. Then I’ll show you how to convert that HEX string back into a comma separated lis of numbers.

Cheers,

Ben

IF OBJECT_ID('dbo.Split') IS NOT NULL
BEGIN
DROP FUNCTION dbo.Split
END
GO

CREATE FUNCTION dbo.Split(@String VARCHAR(MAX), @Delimiter VARCHAR(10))
RETURNS TABLE
AS
RETURN
(
WITH Pointers
AS

(
SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY Number) AS PointerOrder
,Number as Pointer
FROM Sequence
WHERE SUBSTRING(@String, Number, LEN(@Delimiter)) = @Delimiter
)
,Segments
AS
(
SELECT 1 AS Position, Pointer – 1 AS Length
FROM Pointers
WHERE PointerOrder = 1
UNION ALL
SELECT lpointer.pointer + LEN(@Delimiter) + 1 as Position
,rpointer.Pointer – lpointer.Pointer – LEN(@Delimiter) – 1 AS Length
FROM Pointers lpointer
JOIN Pointers rpointer on lpointer.PointerOrder + 1 = rpointer.pointerOrder
UNION ALL
SELECT TOP 1
Pointer + LEN(@Delimiter) + 1 as Position
,LEN(@String) – Pointer – LEN(@Delimiter) AS Length
FROM Pointers
ORDER BY PointerOrder DESC
)
SELECT Position, Length, SUBSTRING(@String, Position, Length) as Item
FROM Segments
)
GO

Select * from dbo.Split(‘1, 2, 3, 4, 5, 6, 7, 8, 9, 10’, ‘, ‘)

Results

Try your own. Mix the numbers up. Use numbers with different lengths.