Hello – I know you can find free Split functions all over the web. But I wrote one anyway. I really like it. It is part of my search engine code which I might get around to sharing some day.
The main reason I wrote this Split? Because I wanted to be able to use a space as a delimiter (can’t do that with LEN(), and with unicode DATALENGTH() returns 2 bytes instead of 1… and I wanted my Split to work with unicode. So, maybe it is lazy or a hack or whatever, but it makes me happy. Like this – 🙂 Don’t forget to use ‘{spc}’ if you want to split on a space.
CREATEFUNCTION [dbo].[Split]
(
@Text nvarchar(MAX),
@Delimiter nvarchar(5)
)
RETURNS @split TABLE (word nvarchar(MAX))
AS
/*
USE
Splits a text string into an “array” of values based on the
delimiter supplied.
@Text: the text to be evaluated
@Delimiter: the delimiter used to ‘split’ the text
RETURNS
A table, where each row is one word/item in the text
HISTORY
2/8/2010 TXC Created
*/
BEGIN
DECLARE @Temp nvarchar(MAX)
DECLARE @TempLen int
DECLARE @DelimiterLen int
IF (@Delimiter=‘ ‘)
BEGIN
INSERTINTO @split SELECT‘Because SQL Server thinks LEN(” ”)=0, to use a space as a delimiter, set @Delimiter=”{spc}”’
RETURN
END
SET @DelimiterLen =LEN(@Delimiter)
SET @Temp = @Text
–funky hack to get around sql server freaking out over spaces as delimiters
IF (@Delimiter=‘{spc}’)
SET @Temp =REPLACE(@Temp,‘ ‘,‘{spc}’)
WHILE 1=1
BEGIN
IF(CHARINDEX(@Delimiter,@Temp)<1)
BREAK;
INSERTINTO @split
VALUES (
RTRIM(LTRIM(SUBSTRING(@Temp, 0 ,CHARINDEX(@Delimiter,@Temp))))
)
SET @TempLen =LEN(@Temp)
SET @Temp =SUBSTRING(@Temp,CHARINDEX(@Delimiter,@Temp)+LEN(@Delimiter), @TempLen)
IF (CHARINDEX(@Delimiter,@Temp)< 1)OR(@Temp=‘ ‘)OR(@Temp=”)
BREAK
END
IFLEN(@Temp)> 0
INSERTINTO @Split
VALUES (RTRIM(LTRIM(@Temp)))
RETURN
END
Enjoy!