Home Forums Article Discussions Article Discussions by Author Discuss content posted by Dwain Camps Splitting Strings Based on Patterns RE: Splitting Strings Based on Patterns

  • November 29, 2012 at 4:21 pm

    #1564090

    Peter H (11/29/2012)

    My version of SQL (2005) does not seem to handle the "VALUES" in

    SELECT TOP(DATALENGTH(@List))

    n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

    FROM

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)

    Could anyone help me expand on this please?

    That form of the VALUES clause came out in 2008. The following can be used as a replacement that will work at approximately the same speed...

    --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to

    -- 10,000... enough to cover VARCHAR(8000)

    WITH E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), --10E+1 or 10 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max

    --===== Generate the numbers from 1 to the length of @List

    SELECT TOP (DATALENGTH(@List))

    n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM E4

    ;

    --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.

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