-- Create Number Table
    IF EXISTS
    (
        SELECT 1
        FROM dbo.sysobjects
        WHERE id = OBJECT_ID(N'dbo.TableOfNumbers')
            AND OBJECTPROPERTY(id, N'IsTable') = 1
    )
        DROP TABLE dbo.TableOfNumbers
GO

    CREATE TABLE dbo.TableOfNumbers
    (
        DummyNumber INT
    )
GO

-- Create work table populated with 0-9       
    IF EXISTS
    (
        SELECT 1
        FROM tempdb.dbo.sysobjects
        WHERE name LIKE '#num%'
    )
        DROP TABLE #num
GO
    SELECT
        '0' AS num
    INTO 
        #num
    UNION ALL 
    SELECT
        '1'
    UNION ALL
    SELECT
        '2'
    UNION ALL
    SELECT
        '3'
    UNION ALL
    SELECT
        '4'
    UNION ALL
    SELECT
        '5'
    UNION ALL
    SELECT
        '6'
    UNION ALL
    SELECT
        '7'
    UNION ALL
    SELECT
        '8'
    UNION ALL
    SELECT
        '9'

-- Use cross join to get 0-999 
-- join in further instances of temp table
-- to get more numbers if required
    INSERT INTO 
        dbo.TableOfNumbers
    SELECT 
        CAST(n1.num + n2.num + n3.num AS INT)
    FROM 
        #num AS n1 
        CROSS JOIN #num AS n2 
        CROSS JOIN #num AS n3 
    ORDER BY 
        n1.num, 
        n2.num, 
        n3.num

-- Create Destination Table
    IF EXISTS
    (
        SELECT 1
        FROM dbo.sysobjects
        WHERE id = OBJECT_ID(N'dbo.Accounts')
            AND OBJECTPROPERTY(id, N'IsTable') = 1
    )
        DROP TABLE dbo.Accounts
GO
    CREATE TABLE dbo.Accounts
    (
        CustomerNo INT, 
        AccountNoCSV VARCHAR(2000)
    )
GO
-- Insert test data
    INSERT INTO 
        dbo.Accounts 
    SELECT 
        1 AS CustomerNo, 
        '123,456' AS AccountNoCSV
    UNION ALL 
    SELECT 
        2, 
        '654,789,1024'
    UNION ALL
    SELECT
        3,  
        '959252,11234,1232'
    UNION ALL
    SELECT
        4, 
        '123425'
    UNION ALL
    SELECT
        5, 
        '12,57324,1,574745,1232,242,252,525235'

-- Return all rows from original table for comparison
    SELECT
        a.CustomerNo, 
        a.AccountNoCSV
    FROM
        dbo.Accounts AS a
    ORDER BY
        a.CustomerNo

-- return resultset containing
-- one row per account number
    SELECT 
        a.CustomerNo, 
        SUBSTRING(
                    a.AccountNoCSV,
                    ton.DummyNumber, 
                    (COALESCE(
                                NULLIF(ton2.DummyNumber, 0), 
                                LEN(a.AccountNoCSV)+2
							 )-1
                     ) - ton.DummyNumber
                 ) AS AccountNo
    FROM dbo.Accounts AS a
    JOIN TableOfNumbers AS ton
    ON ton.DummyNumber = CHARINDEX( ',', 
                                    ','+a.AccountNoCSV, 
                                    CHARINDEX(  ',',
                                                ','+a.AccountNoCSV,
                                                (TON.DummyNumber-1)
                                              )
                                   )
    JOIN TableOfNumbers AS ton2
    ON ton2.DummyNumber = CHARINDEX( ',', 
                                     ','+a.AccountNoCSV, 
                                     CHARINDEX(  ',',
                                                 ','+a.AccountNoCSV,
                                                 (TON.DummyNumber)
                                               )+1
                                   )
    ORDER BY
        a.CustomerNo, 
        ton.DummyNumber