T-SQL Split function

1 בפברואר 2009

24 תגובות

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:

 image

Summary

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.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

24 תגובות

  1. BxB2 בפברואר 2009 ב 19:34

    another way using a CTE:

    CREATE FUNCTION [dbo].[Split]
    (
    String nvarchar(max), @Delimiter nvarchar(max) = ','
    )
    RETURNS TABLE AS
    RETURN
    WITH csvtbl(start, stop) AS (
    SELECT start = convert(bigint, 1),
    stop = charindex(@Delimiter, @String + @Delimiter)
    UNION ALL
    SELECT start = stop + 1,
    stop = charindex(@Delimiter,
    @String + @Delimiter, stop + 1)
    FROM csvtbl
    WHERE stop > 0
    )
    SELECT ltrim(rtrim(substring(@String, start,
    CASE WHEN stop > 0 THEN stop – start ELSE 0 END)))
    AS Item
    FROM csvtbl
    WHERE stop > 0
    ;

    להגיב
  2. Sean2 בפברואר 2009 ב 22:38

    I've always wondered why no split function was included, natively. It's such a useful function.

    להגיב
  3. Ruti9 ביולי 2009 ב 10:06

    Great function!

    helped me a lot!

    להגיב
  4. Shay Golan23 ביולי 2009 ב 23:10

    Hi Itai,
    Thanks for this code,
    i changed your code so i can get selected value from the string
    hope it will help others
    Shay

    Use the Function
    ============
    Set @DataRow = 'Data1|Data2|Data3'
    Set @var=(select dbo.fn__SplitValue(@DataRow, '|', 3))

    – >>> @var=Data3

    – ================================================
    – Description: Split String and get Selected Value
    ================================================
    Create FUNCTION [dbo].[fn__SplitValue]
    (

    @DataRow VARCHAR(max), — Full string to split
    @Delimeter nvarchar(max), — Spliter
    @PlaceId int — place of value in split datarow to return
    )
    RETURNS VARCHAR(max)
    AS
    BEGIN

    – Declare parameters
    DECLARE @DataValue VARCHAR(max)
    DECLARE @FoundIndex INT
    DECLARE @Iterator INT = 1 — Place finder

    SET @FoundIndex = CHARINDEX(@Delimeter,@DataRow)
    WHILE (@FoundIndex>0) — loop splited values
    begin

    if(@Iterator=@PlaceId)
    begin
    — found the place of selected value in string split
    Set @DataValue = LTRIM(RTRIM(SUBSTRING(@DataRow, 1, @FoundIndex – 1)))
    end

    — goto Next value
    SET @DataRow = SUBSTRING(@DataRow, @FoundIndex + DATALENGTH(@Delimeter) / 2, LEN(@DataRow))
    SET @Iterator = @Iterator + 1
    SET @FoundIndex = CHARINDEX(@Delimeter, @DataRow)
    END

    if(@Iterator=@PlaceId)
    Begin
    – Last value has no Delimeter after
    Set @DataValue = LTRIM(RTRIM(@DataRow))
    End

    – Return the result of the function
    RETURN @DataValue

    END

    להגיב
  5. dave20 באוגוסט 2009 ב 17:46

    can you tell me where MAX is decalred? Is this specific to 2005?

    להגיב
  6. Lucy1 בספטמבר 2009 ב 19:31

    This function is really helpful. Thank you so much.

    להגיב
  7. itai3 בספטמבר 2009 ב 8:28

    Hi dave,

    the ability to define the variable as VARCHAR(MAX) / NVARCHAR(MAX) was added in SQL Server 2005.
    you can continue reading more about it here: http://www.teratrax.com/articles/varchar_max.html

    להגיב
  8. Windy8 בספטמבר 2009 ב 9:59

    Thanks…..

    You solved my problem….

    להגיב
  9. Jim Carnicelli26 באוקטובר 2009 ב 15:41

    Excellent. I've added this to one of my projects with a header comment giving you credit. Thanks for sharing.

    להגיב
  10. Bebandit16 בפברואר 2010 ב 23:48

    This function works well! Thanks for posting it!

    להגיב
  11. Omer5 במאי 2010 ב 11:22

    Thanks for the code. Very useful.

    להגיב
  12. re: T-SQL Split function27 ביוני 2010 ב 4:41

    golden

    להגיב
  13. Dave Naismith30 בספטמבר 2010 ב 15:57

    Thanks a lot. Great solution

    להגיב
  14. M Dicken5 באוקטובר 2010 ב 16:57

    Thankyou for the SQL Split Function … it helped me out :-) … cheer

    להגיב
  15. Jeff Moden7 במרץ 2011 ב 6:32

    The following code is about 3 times faster. Give it a try.

    CREATE FUNCTION dbo.XmlDelimitedSplit
    (
    @String VARCHAR(MAX),
    @Delimiter CHAR(1)
    )
    RETURNS @Return TABLE (ItemNumber INT, ItemValue VARCHAR(max))
    AS
    BEGIN

    DECLARE @Xml XML;
    SELECT @Xml = '' + replace(@text, @delimiter, '') + ''
    ;
    INSERT INTO @Return
    SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
    ItemValue = item.value('text()[1]', 'varchar(max)')
    FROM @Xml.nodes('//r') R(item)
    ;
    RETURN
    ;
    END
    ;
    –Jeff Moden

    להגיב
  16. Marianne Daye14 באפריל 2011 ב 21:33

    This is a really helpful solution for my purposes, and easy to implement. Thanks!

    להגיב
  17. sqlqueen24 באפריל 2011 ב 18:18

    I have string that has [Error] some error 1 [error] some error 2
    and the datalenght is 1319433 and it only prints out 22 lines of errors and there are over 1800 errros to report.

    Any ideas

    להגיב
  18. Pratik1 בנובמבר 2011 ב 9:02

    I want to split values of string into multiple columns instead of rows … anybody has any idea ?

    להגיב
  19. Beddel28 בדצמבר 2011 ב 12:30

    Thank you very much!

    להגיב
  20. Lukeni212 בינואר 2012 ב 16:48

    Very usefull function. It works fine. Thanks for that

    להגיב
  21. Sourav Kings27 ביוני 2012 ב 15:25

    Thanks a lot Mr. Goldstein. You made many people's life a lot easier with this piece of fantastic code of Split functionality.

    להגיב
  22. Hammett7 באוגוסט 2012 ב 11:39

    Can I only say what relief to get someone that in fact knows what theyre discussing on line.
    You definitely realize how to bring a difficulty to light and work
    out it crucial. The best path to ought to check this
    out and appreciate this side on the story. I cant believe youre no more wide-ranging since you also definitely give out the gift.

    להגיב
  23. Ed Morris 22 באוגוסט 2012 ב 20:20

    Thanks for the function and explanation it made life alot simpler I will also add that the parameters many need to be joined as specified in Munish Bansal's blog: (http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/).

    Thanks again.

    להגיב