During one of the projects I worked on, I had to parse a text which was passed from the application in my T-SQL code. I found that T-SQL had no built-in string system function for this operation, which I find rather useful, especially in cases you need to perform some batch based operations (which helps accelerate your application performance by reducing round trips to the DB).
So I came up with the following solution:
CREATE FUNCTION [dbo].[Split] ( @RowData NVARCHAR(MAX), @Delimeter NVARCHAR(MAX) ) RETURNS @RtnValue TABLE ( ID INT IDENTITY(1,1), Data NVARCHAR(MAX) ) AS BEGIN DECLARE @Iterator INT SET @Iterator = 1 DECLARE @FoundIndex INT SET @FoundIndex = CHARINDEX(@Delimeter,@RowData) WHILE (@FoundIndex>0) BEGIN INSERT INTO @RtnValue (data) SELECT Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1))) SET @RowData = SUBSTRING(@RowData, @FoundIndex + DATALENGTH(@Delimeter) / 2, LEN(@RowData)) SET @Iterator = @Iterator + 1 SET @FoundIndex = CHARINDEX(@Delimeter, @RowData) END INSERT INTO @RtnValue (Data) SELECT Data = LTRIM(RTRIM(@RowData)) RETURN END
Note that this implementation uses DATALENGTH function instead of the LEN function, and that is due to the fact that LEN doesn’t consider the leading spaces in the given string variable – this can harm the parsed result. Another difference between DATALENGTH and LEN is that DATALENGTH returns the result as the number of bytes used where LEN returns the result as number of characters, and that is the reason we should divide the DATALENGTH result with 2.
Here is an example of how to use the Split function:
DECLARE @Str NVARCHAR(MAX) SELECT @Str = 'This#$#is#$#my#$#test' SELECT * FROM [dbo].[Split] (@Str, '#$#')
And here are the results:
In this post I reviewed my implementation for T-SQL string Split function which you may find useful when parsing strings in T-SQL, especially in cases you would like to improve your application performance by doing some batch based operations.
Hope you’ll find it useful.