Home Forums SQL Server 2005 T-SQL (SS2K5) split column into multiple records based on substring fixed lenght RE: split column into multiple records based on substring fixed lenght

  • CREATE TABLE [dbo].[tableA](

    [CUST] [varchar](9) NOT NULL,

    [WPTEXT] [varchar](4000) NULL)

    INSERT INTO tableA values ('12345' ,

    '6/26/08 BAD DEBT 1,351.28 BANKRUPT C-11' + CHAR(10) +

    '07/03/08 BAD DEBT RECOVERY 273.07 BAL OWED 1,078.21.' + CHAR(10) +

    '09/17/09 BAD DEBT RECOVERY 462.07 BAL OWED 616.14.' + CHAR(10) +

    '06/29/09 OVER PAYMENTS USED AGAINST BAD DEBT. (196.81)' + CHAR(10) +

    'BALANCE OWED 419.33.' + CHAR(10) +

    '11/30/09 BAD DEBT RECOVERY 1.70 BAL OWED 417.63.' + CHAR(10) +

    '12/23/10-BAD DEBT RECOVERY 10.20 BAL OWED 407.43.')

    SELECT cust, wptext, charindex(char(10), wptext) as firstposlinefeed

    FROM tableA where charindex(char(10), wptext) <> 0

    Hope this code is helpful