Post reply

effective way to break a string into rows basing on lenght of string

  • February 27, 2013 at 9:47 am

    #274580

    /*Current table that has a Primary key and a text column, need to break this text into rows if the len > 15*/

    select '1' as PK,'Physical inspection Check line cord & plug Perform calibration procedure ESI #102 SCOPE' text union

    select '2','test1'

    /*This is what I want to achive*//*Break the text when the length of text > 15 into seprate rows */

    select '1' as PK,'1' as SequenceOrder,'Physical inspec' as text union

    select '1','2','tion Check lin' union

    select '1','3','e cord & plug ' union

    select '1','4','Perform calibra' union

    select '1','5','tion procedure ' union

    select '1','6',' ESI #102 SCOP' union

    select '1','7','E' union

    select '2','1','test1'

    the table in the db is huge with a lot of records more than 200000. Is there a way to do this keeping in mind an effective run time

  • February 27, 2013 at 11:34 am

    #1591667

    Hi

    This should do the trick. You will need to make the tally big enough to split your largest string

    ;with smallTally AS (

    SELECT row_number() OVER (ORDER BY (SELECT NULL)) N FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as T(N)

    )

    ,testData AS (

    SELECT * FROM (VALUES (1,'This is a long string to be split multiple times hopefully'), (2,'Cant touch me')) AS d(id,test)

    )

    SELECT id

    ,n partNum --removed unneeded row_number

    ,substring(test, ((N -1) * 15) + 1, 15) testOut

    FROM testData d

    CROSS APPLY (SELECT TOP ((len(test) / 15) + 1) N FROM smallTally ORDER BY N) a --Added order by

  • February 27, 2013 at 12:13 pm

    #1591674

    I have no clue how this works.. :blink:

    Pls could u break it down as to how can i use it

  • February 27, 2013 at 12:57 pm

    #1591706

    Essentially what it is doing is, using the long string as an example.

    Take the length of the text and divide it by 15 and add one. This gives us the amount of portions the text needs to be split into.

    58 divided by 15 equals 3, plus 1 equals 4 portions required

    Join to the smallTally table that many times using a TOP in a cross apply.

    Get the portion of the string starting from the value of N (1 to 4) minus 1 (to shift back to start) times 15 add 1

    This will give us start locations of 1, 16, 31, 46 for the substring

    Do the substring from this position for a length of 15.

    I've expanded the query a bit here to try and demonstrated what it is doing. I've also changed it slightly to include an order by in the cross apply and removed the surplus row_number in the main query.

    ;with smallTally AS ( -- This is a query to build a small tally table with values 1 to 10

    SELECT row_number() OVER (ORDER BY (SELECT NULL)) N FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as T(N)

    )

    ,testData AS ( -- Some test data

    SELECT * FROM (VALUES (1,'This is a long string to be split multiple times hopefully'), (2,'Cant touch me')) AS d(id,test)

    )

    SELECT d.id

    ,len(test) textLength

    -- this will return an integer, so for a text length 13 we get 13 / 15 (= 0) + 1 (= 1)

    -- for 58 we get (58 / 15) (= 3) + 1 (= 4)

    ,(len(test) / 15) + 1 NumberOfPortionsRequired

    ,((N - 1) * 15) + 1 substringStartedAt -- For n = 1 we get (0 * 15) + 1 = 1, For n=2 we get (1 * 15) + 1 = 16 ...

    ,a.n partnum -- taken from smalltally

    ,substring(d.test, ((N -1) * 15) + 1, 15) testout --Get the substring of test starting

    FROM testData d

    CROSS APPLY (

    SELECT TOP ((len(test) / 15) + 1) -- Select the number of portions required from smallTally and apply to the testdata row

    N

    FROM smallTally

    ORDER BY N

    ) a

    I hope this makes it a bit clearer:unsure:

  • February 27, 2013 at 10:23 pm

    #1591863

    knakka 14475 (2/27/2013)

    I have no clue how this works.. :blink:

    Pls could u break it down as to how can i use it

    The article at the following link explains how a Tally Tabl can be used as a high speed replacement for certain types of loops.

    http://www.sqlservercentral.com/articles/T-SQL/62867/

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

  • February 28, 2013 at 12:25 pm

    #1592158

    I have learned something new.. thanks to you mickyT.. 🙂

  • February 28, 2013 at 12:29 pm

    #1592162

    Thanks Jeff Moden for the article

  • March 1, 2013 at 6:48 am

    #1592349

    knakka 14475 (2/28/2013)

    Thanks Jeff Moden for the article

    You bet. Thank you for the feedback.

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

Viewing 8 posts - 1 through 7 (of 7 total)

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