Pattern-based split string

“hickymanz” asked in the SQL Server Central forums for a method of counting unique words in a text column. Wayne Lawton recommended using a string split function, which was a good idea, but not quite adequate for the job in my opinion.

Typical string split functions, like this one that I wrote can handle only a single delimiter, e.g. a comma. But in the case of splitting for unique words you want all sorts of possible delimiters — punctuation marks, white space including spaces, possibly numerics, etc.

I believe I’ve seen other, similar requests for getting unique words before, so I decided to solve the problem. I modified that split string function to accept a pattern of valid characters for the second argument. Anything NOT in the pattern will be treated as a delimiter. I figured that would be a bit more flexible than the other way around, so that people don’t have to type in (or figure out) every single possible white space and/or punctuation character.

Anyway, I think the modification is pretty straightforward if you understand the previous function, so check it out and if you have any questions feel free to ask. Here is the function:

[sql]CREATE FUNCTION dbo.SplitStringPattern
(
@List TEXT,
@Pattern VARCHAR(50)
)
RETURNS @ReturnTbl TABLE (OutParam VARCHAR(20))
WITH SCHEMABINDING
AS
BEGIN
DECLARE @LeftSplit VARCHAR(7998)
DECLARE @SplitStart INT SET @SplitStart = 0
DECLARE @SplitEnd INT
SET @SplitEnd = 7998

SET @Pattern = ‘%’ + ‘[^’ + RIGHT(@Pattern, LEN(@Pattern) – 1) + ‘%’

DECLARE @Delimiter CHAR(1)
SELECT @Delimiter = CHAR(MAX(Number))
FROM dbo.Numbers
WHERE CHAR(Number) LIKE @Pattern

SELECT @SplitEnd = MAX(Number)
FROM dbo.Numbers
WHERE (SUBSTRING(@List, Number, 1) LIKE @Pattern
OR Number = DATALENGTH(@List) + 1)
AND Number BETWEEN @SplitStart AND @SplitEnd

WHILE @SplitStart < DATALENGTH(@List) – 1
BEGIN
SET @LeftSplit = @Delimiter + SUBSTRING(@List, @SplitStart, @SplitEnd – @SplitStart) + @Delimiter

INSERT @ReturnTbl (OutParam)
SELECT LTRIM(RTRIM(SUBSTRING(@LeftSplit, Number + 1,
PATINDEX(@Pattern, SUBSTRING(@LeftSplit, Number + 1, LEN(@LeftSplit))) – 1)))
AS Value
FROM dbo.Numbers
WHERE Number <= LEN(@LeftSplit) – 1
AND SUBSTRING(@LeftSplit, Number, 1) LIKE @Pattern
AND SUBSTRING(@LeftSplit, Number + 1,
PATINDEX(@Pattern, SUBSTRING(@LeftSplit, Number + 1, LEN(@LeftSplit))) – 1) <> ”

SET @SplitStart = @SplitEnd + 1
SET @SplitEnd = @SplitEnd + 7998

SELECT @SplitEnd = MAX(Number) + @SplitStart
FROM dbo.Numbers
WHERE (SUBSTRING(@List, Number + @SplitStart, 1) LIKE @Pattern
OR Number+@SplitStart = DATALENGTH(@List) + 1)
AND Number BETWEEN 1 AND @SplitEnd – @SplitStart
END

RETURN
END
[/sql]

A note on the pattern for input: The pattern should be single-character based, and must be delimited with [ ]. Also, you must type in the actual characters in the pattern. I do not recommend using ranges; ranges cause strange side-effects because they tend to include (depending on collation) characters that you wouldn’t expect to show up (e.g. characters with umlauts over them). So unless that’s what you really want, don’t use ranges.

Here’s a usage example:

[sql]SELECT OutParam
FROM dbo.SplitStringPattern(‘This is a test.’, ‘[abcdefghijklmnopqrstuvwxyz]’)

OutParam
————-
This
is
a
test
[/sql]

Enjoy!