split column into multiple records based on substring fixed lenght

  • I have seen posts to split column into multiple records based on delimited value.

    I need to split a vchar(4000) column into multiple records into column vchar(80)

    based on substring if not blank

    I can use the below code...would be okay if original column was only vchar(400)

    but obviously there has to be a better way to loop thru.

    Thanks for the help in advance.

    SELECT cust, SUBSTRING(wptext, 1, 80) AS wptext, 1 as seq

    FROM TableA WHERE SUBSTRING(wptext, 1, 80) <> ' '

    UNION ALL

    SELECT cust, SUBSTRING(wptext, 81, 80) AS wptext, 2 as seq

    FROM TableA WHERE SUBSTRING(wptext, 81, 80) <> ' '

    UNION ALL

    SELECT cust, SUBSTRING(wptext, 161, 80) AS wptext, 3 as seq

    FROM TableA WHERE SUBSTRING(wptext, 161, 80) <> ' '

    UNION ALL

    SELECT cust, SUBSTRING(wptext, 241, 80) AS wptext, 4 as seq

    FROM TableA WHERE SUBSTRING(wptext, 241, 80) <> ' '

    UNION ALL

    SELECT cust, SUBSTRING(wptext, 321, 80) AS wptext, 5 as seq

    FROM TableA WHERE SUBSTRING(wptext, 321, 80) <> ' '

    order by cust, seq

  • A Tally table (or in this case a cteTally) can come to the rescue.

    Check the following code and ask for anything that you don't understand.

    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

    ),

    cteTally(n) AS(

    SELECT TOP 50 ROW_NUMBER() OVER( ORDER BY ( SELECT NULL))

    FROM E1, E1 b

    )

    SELECT cust, SUBSTRING(wptext, (80 * (n-1)) + 1, 80 ) AS wptext, n as seq

    FROM TableA a

    JOIN cteTally t ON t.n <= CEILING( LEN( a.wptext) / 80.0)

    order by cust, n

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • SELECT x.*

    FROM TableA

    CROSS APPLY (

    SELECT cust, SUBSTRING(wptext, 1, 80), 1 UNION ALL

    SELECT cust, SUBSTRING(wptext, 81, 80), 2 UNION ALL

    SELECT cust, SUBSTRING(wptext, 161, 80), 3 UNION ALL

    SELECT cust, SUBSTRING(wptext, 241, 80), 4 UNION ALL

    SELECT cust, SUBSTRING(wptext, 321, 80), 5

    ) x (cust, wptext, seq)


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • The Tally Table appears to be what I am looking to do.

    I will verify some of the results but initial return values look okay.

    So the code has several "SELECT 1 UNION ALL"...how do you know how many to use?

    The code also uses "SELECT TOP 50 ROW_NUMBER()"....how do you know to use 50?

    Thanks again for the help!

  • on side note....how do you post the script inside the window box. I read best practice for posting but not seeing it.

    Do you use the IFCode Shortcuts...still not quite sure how those work either.

  • All the "SELECT 1 UNION ALL" create 10 rows which I later use to create 100 rows with a CROSS JOIN in the next CTE.

    I'm using TOP 50 because you said that you have a varchar(4000) and 4000/80 = 50.

    You can change the value to any value you need or just remove it.

    To put your code in the code box you use the IFCode Shortcuts. Either you click on the [ code="sql"] one before inserting the code and then put the code between the labels or you can select the code and click on the shortcut to put the labels around your selection.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • So I was able to split column based on specific substring length and export the data to my target source; at which point I noticed data contained Line Feed.

    So is there a way to split column into multiple records based on Line Feed and excluding Line Feed char(10)

    Thanks again in advance for your help.

  • I need you to post sample data and expected results, because I'm not sure that I understand the problem. To know how to do it, please read the article linked on my signature.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Richard Cranston (12/12/2013)


    So I was able to split column based on specific substring length and export the data to my target source; at which point I noticed data contained Line Feed.

    So is there a way to split column into multiple records based on Line Feed and excluding Line Feed char(10)

    Thanks again in advance for your help.

    See Jeff Moden's splitter at http://www.sqlservercentral.com/articles/Tally+Table/72993/

    and use CHAR(10) as the delimiter

    Far away is close at hand in the images of elsewhere.
    Anon.

  • 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

  • I was able to use Jeff Moden's splitter at http://www.sqlservercentral.com/articles/Tally+Table/72993/

    and use CHAR(10) as the delimiter, and the results are not all bunched up; but I can still end up with a

    split over 70 chars long...so I really need to combine both....Thanks to everyone's help!

  • It's easy to combine both techniques. Here's an example returning rows with 4 characters or less.

    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

    ),

    cteTally(n) AS(

    SELECT TOP 50 ROW_NUMBER() OVER( ORDER BY ( SELECT NULL))

    FROM E1, E1 b

    )

    SELECT cust,

    SUBSTRING(s.Item, (40 * (t.n-1)) + 1, 40 ),

    LEN(s.Item),

    LEN(SUBSTRING(s.Item, (40 * (t.n-1)) + 1, 40 ))

    FROM tableA a

    CROSS APPLY dbo.DelimitedSplit8K( wptext, CHAR(10)) s

    JOIN cteTally t ON t.n <= CEILING( LEN( s.Item) / 40.0)

    ORDER BY ItemNumber, n

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks Luis for the posting of both combinations.....that is great!! Just what I needed.

    Thanks for Jeff Modine splitter.

    I am always learning new things!!

  • I need to generate the Sequence Number from both combined data; if I use "n" value from the order by,

    then I get for example: 1,2,3,4 from split data and then 1,2 from the column length 40 data (total 6 records for a specific cust).

    I need to get 1,2,3,4,5,6 instead of 1,2,3,4,1,2.

    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

    ),

    cteTally(n) AS(

    SELECT TOP 50 ROW_NUMBER() OVER( ORDER BY ( SELECT NULL))

    FROM E1, E1 b

    )

    SELECT cust,

    SUBSTRING(s.Item, (40 * (t.n-1)) + 1, 40 ),

    n as Seq, <---- I tried adding this but not correct

    LEN(s.Item),

    LEN(SUBSTRING(s.Item, (40 * (t.n-1)) + 1, 40 ))

    FROM tableA a

    CROSS APPLY dbo.DelimitedSplit8K( wptext, CHAR(10)) s

    JOIN cteTally t ON t.n <= CEILING( LEN( s.Item) / 40.0)

    ORDER BY ItemNumber, n

  • You need to add an additional ROW_NUMBER() instead of the previously generated n.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 1 through 14 (of 14 total)

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