Splitting strings

,

There are many split-functions out there. This is very fast because there is only two string manipulation functions involved, and not per-part string manipulation.

CREATE FUNCTION dbo.fnParseList
(
	@Delimiter CHAR,
	@Text VARCHAR(8000)
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
	DECLARE	@NextPos SMALLINT,
		@LastPos SMALLINT

	SELECT	@NextPos = 0

	WHILE @NextPos <= DATALENGTH(@Text)
		BEGIN
			SELECT	@LastPos = @NextPos,
				@NextPos =	CASE
							WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
							ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
						END

			INSERT	@Result
				(
					Data
				)
			SELECT	SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
		END
		
	RETURN
END

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)