chomping on ;

WITH CTE (RECORDID, COMMENT, LONG_TEXT, COL_ORDER)    AS(
        SELECT
            RECORDID
        --, LEFT(LONG_TEXT, 200)                                                                                                     -- 200 char chunk. this is max.
        --, REVERSE(LEFT(LONG_TEXT, 200))                                                                                            -- flip it around
        --, CHARINDEX(';', REVERSE(LEFT(LONG_TEXT, 200)))                                                                            -- find the first (last) ;
        --, RIGHT(REVERSE(LEFT(LONG_TEXT, 200)), 200-CHARINDEX(';', REVERSE(LEFT(LONG_TEXT, 200))))                                  -- bite off what's after (before) the ;
        ,   REVERSE(RIGHT(REVERSE(LEFT(LONG_TEXT, 200)), 201-CHARINDEX(';', REVERSE(LEFT(LONG_TEXT, 200))))) AS  COMMENT             -- flip around again to get real string
        --,LEN(REVERSE(RIGHT(REVERSE(LEFT(LONG_TEXT, 200)), 201 - CHARINDEX(';', REVERSE(LEFT(LONG_TEXT, 200))))))                   -- how long is the string?
        ,   SUBSTRING(LONG_TEXT, LEN(REVERSE(RIGHT(REVERSE(LEFT(LONG_TEXT, 200)), 201 - CHARINDEX(';', REVERSE(LEFT(LONG_TEXT, 200)))))) + 1, LEN(LONG_TEXT))  AS  COMMENT_REMAIN  -- chomp off parsed string and get the remainder
        ,   COL_ORDER
        FROM
            [VARIABLES2]
        WHERE  1 =  1
            AND COL_ORDER =  1
        UNION ALL
        SELECT
            RECORDID
        ,   REVERSE(RIGHT(REVERSE(LEFT(LONG_TEXT, 200)), 201 - CHARINDEX(';', REVERSE(LEFT(LONG_TEXT, 200)))))  AS  COMMENT
        ,   SUBSTRING(LONG_TEXT, LEN(REVERSE(RIGHT(REVERSE(LEFT(LONG_TEXT, 200)), 201 - CHARINDEX(';', REVERSE(LEFT(LONG_TEXT, 200)))))) + 1, LEN(LONG_TEXT))  AS  COMMENT_REMAIN
        ,   COL_ORDER + 1
        FROM
            CTE
        WHERE  1 =  1
        AND LONG_TEXT  !=  ''
    )
SELECT
        RECORDID
      , Max(Case When Col_Order='1' then comment End) as [comment_1]
      , Max(Case When Col_Order='2' then comment End) as [comment_2]
      , Max(Case When Col_Order='3' then comment End) as [comment_3]
      , Max(Case When Col_Order='4' then comment End) as [comment_4]
      , Max(Case When Col_Order='5' then comment End) as [comment_5]
      , Max(Case When Col_Order='6' then comment End) as [comment_6]
      , Max(Case When Col_Order='7' then comment End) as [comment_7]
      , Max(Case When Col_Order='8' then comment End) as [comment_8]
      , Max(Case When Col_Order='9' then comment End) as [comment_9]
      , Max(Case When Col_Order='10' then comment End) as [comment_10]
FROM
    [CTE]
GROUP BY
    RECORDID