SPLIT Function

  • Comments posted to this topic are about the item SPLIT Function

  • Umm, you might want to change your description. The COALESCE function built into SQL Server does not create a concatenated string out of a list of values. From BOL:

    COALESCE

    Returns the first nonnull expression among its arguments.

    If you're referring to a COALESCE UDF, you might want to try to point the user to that function as well.

    Just FYI - presumably, I'm the only one of the hundreds of people who read this who's anal-retentive enough to bring it up....


    R David Francis

  • Heh... my concern is that UDF's of this nature qualify as RBAR... you get to split exactly one row. That's fine for a single parameter passed in from a GUI, but if you want to split a whole column in a table, you can use a Tally table to do the whole table at once. For example...

    --===== Create a test table and some CSV data

    -- This is NOT part of the solution... just building test data here

    CREATE TABLE #CsvTest (SomeID INT PRIMARY KEY, SomeCsv VARCHAR(200))

    INSERT INTO #CsvTest

    (SomeID,SomeCsv)

    SELECT 1,'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' UNION ALL

    SELECT 2,'Part01,,Part03,Part04,Part05,Part06,Part07,,Part09,Part10' UNION ALL

    SELECT 3,'Part01,Part02,Part03,,Part05,Part06,Part07,Part08,Part09,Part10' UNION ALL

    SELECT 4,'Part01,Part02,Part03,Part04,Part05,,Part07,Part08,Part09,Part10' UNION ALL

    SELECT 5,'Part01' UNION ALL

    SELECT 6,'Part01,Part02' UNION ALL

    SELECT 7,',Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,' UNION ALL

    SELECT 8,NULL UNION ALL

    SELECT 9,'' UNION ALL

    SELECT 10,','

    --===== Split the whole CSV column using a single select with a cross join to the Tally table.

    SELECT SomeID,

    ElementNum = t.N-LEN(REPLACE(LEFT(','+ct.SomeCsv+',',t.N), ',', '')),

    Value = NULLIF(SUBSTRING(','+ct.SomeCsv+',',t.N+1,CHARINDEX(',',','+ct.SomeCsv+',',t.N+1)-t.N-1),' ')

    FROM #CsvTest ct

    CROSS JOIN dbo.Tally t

    WHERE t.N < LEN(','+ct.SomeCsv+',')

    AND SUBSTRING(','+ct.SomeCsv+',',N,1) = ','

    ORDER BY SomeID,ElementNum

    DROP TABLE #CsvTest

    ...and, just in case you don't know how to make the wonderful little helper table known as a Tally table (it's got LOTS of uses), here's how...

    --===== Create and populate the Tally table on the fly

    SELECT TOP 11000 --equates to more than 30 years of dates

    IDENTITY(INT,1,1) AS N

    INTO dbo.Tally

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

    ALTER TABLE dbo.Tally

    ADD CONSTRAINT PK_Tally_N

    PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

    --===== Allow the general public to use it

    GRANT SELECT ON dbo.Tally TO PUBLIC

    Of course, we all know it's a mortal database sin to store delimited data in a column... right? 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply