Oracle: New line after 50 characters

Experts,

I am trying to structure my lengthy summary data in my TKT_SUMMARY field to not exceed 50 characters for each line.  Which should evaluate if there is a space at the end of the 50 characters so that it creates a new line after complete words.

I read I can do something like this:
SUBSTR(TKT_SUMMARY,1,50)||chr(10)||SUBSTR(TKT_SUMMARY,51)

The problem is that it evaluates the first line only.

Any ideas?
Maliki HassaniAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
This is almost always handled by the application displaying the data.

Can you not control the text wrapping at display time?
What application is displaying the data?
0
Maliki HassaniAuthor Commented:
I am displaying the data using ESRI's ArcMap.  It's a data grid that shows ticket details for specific areas on the map.  Currently the summary field extends out and doesn't wrap the text.  I maybe able to get the admin to write the code in the application.  Unfortunately, I don't have access to the server it lives on I can only manipulate the table structure of the data.
0
slightwv (䄆 Netminder) Commented:
Breaking on a word is the difficult part.

There may be some SQL magic but I'm thinking this may require a user-defined function.

I'll see if I can get a little time to work on this later.  Hopefully some of Experts Exchange SQL magicians will arrive later.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Maliki HassaniAuthor Commented:
Okay great.  I will keep looking  as well.
0
johnsoneSenior Oracle DBACommented:
I found something that might work, but it starts to get scary after 2 pieces.  What is the maximum length of the field?  Also, do you want to break at the space before character 50 or the first one after character 50?

If you want before, this will give you the first piece:

substr(tkt_summary, 1, instr(tkt_summary, ' ', 50-length(tkt_summary), 1))

Then this will give you the second piece:

substr(tkt_summary, instr(tkt_summary, ' ', 50-length(tkt_summary), 1)+1, instr(tkt_summary, ' ', instr(tkt_summary, ' ', 50-length(tkt_summary), 1)-length(tkt_summary), 1))

After that it starts to get pretty scary.  I don't want to try to go further until I know if you are looking for space before or after 50 characters and how big the field is.  If it is a 4000 character field, then the substr/instr approach isn't going to be feasible.
0
Maliki HassaniAuthor Commented:
thanks, johnsone.

It's not a specific character really, it is just a way to make the field not look so long.  So any idea that is easy to do , will work.

The summary field is set to max at 2000 characters.

Thanks
0
johnsoneSenior Oracle DBACommented:
The query below works.  The catch is that it is horribly inefficient.  It will break the column up into parts no more than 100 characters long and break on a space.  Since there are 2000 characters max, that means 20 columns.  However since we break before 100 characters, it could mean 21 (or 22 but hopefully not).  If anything comes back in the last TKT_SUMMARY column, then we went to 22 columns and have a problem.  You will have to change the name of the table in the last line.  And any WHERE clause that you want to add should go inside the inner most subquery.

SELECT p1, 
       p2, 
       p3, 
       p4, 
       p5, 
       p6, 
       p7, 
       p8, 
       p10, 
       p11, 
       p12, 
       13, 
       14, 
       15, 
       16, 
       17, 
       18, 
       19, 
       20, 
       Substr(tkt_summary, 1, CASE 
                                WHEN Length(tkt_summary) <= 100 THEN 100 
                                ELSE Instr(tkt_summary, ' ', 
                                     100 - Length(tkt_summary), 
                                     1) 
                              END) p21, 
       CASE 
         WHEN Length(tkt_summary) <= 100 THEN NULL 
         ELSE Substr(tkt_summary, Instr(tkt_summary, ' ', 100-Length(tkt_summary 
                                  ), 1) 
                                  + 1) 
       END                         tkt_summary 
FROM   (SELECT p1, 
               p2, 
               p3, 
               p4, 
               p5, 
               p6, 
               p7, 
               p8, 
               p10, 
               p11, 
               p12, 
               13, 
               14, 
               15, 
               16, 
               17, 
               18, 
               19, 
               Substr(tkt_summary, 1, CASE 
                                        WHEN Length(tkt_summary) <= 100 THEN 100 
                                        ELSE Instr(tkt_summary, ' ', 
                                             100 - Length(tkt_summary), 
                                             1) 
                                      END) p20, 
               CASE 
                 WHEN Length(tkt_summary) <= 100 THEN NULL 
                 ELSE Substr(tkt_summary, Instr(tkt_summary, ' ', 100-Length( 
                                          tkt_summary), 1) 
                                          + 1) 
               END                         tkt_summary 
        FROM   (SELECT p1, 
                       p2, 
                       p3, 
                       p4, 
                       p5, 
                       p6, 
                       p7, 
                       p8, 
                       p10, 
                       p11, 
                       p12, 
                       13, 
                       14, 
                       15, 
                       16, 
                       17, 
                       18, 
                       Substr(tkt_summary, 1, CASE 
                                                WHEN Length(tkt_summary) <= 100 
                                              THEN 
                                                100 
                                                ELSE Instr(tkt_summary, ' ', 
                                                     100 - Length(tkt_summary), 
                                                     1) 
                                              END) p19, 
                       CASE 
                         WHEN Length(tkt_summary) <= 100 THEN NULL 
                         ELSE Substr(tkt_summary, Instr(tkt_summary, ' ', 100 
                                                  -Length( 
                                                  tkt_summary), 1) 
                                                  + 1) 
                       END                         tkt_summary 
                FROM   (SELECT p1, 
                               p2, 
                               p3, 
                               p4, 
                               p5, 
                               p6, 
                               p7, 
                               p8, 
                               p10, 
                               p11, 
                               p12, 
                               13, 
                               14, 
                               15, 
                               16, 
                               17, 
                               Substr(tkt_summary, 1, CASE 
                                                        WHEN 
                               Length(tkt_summary) <= 100 
                                                      THEN 
                                                        100 
                                                        ELSE 
                               Instr(tkt_summary, ' ', 
                               100 - Length(tkt_summary), 
                               1) 
                                                      END) p18, 
                               CASE 
                                 WHEN Length(tkt_summary) <= 100 THEN NULL 
                                 ELSE Substr(tkt_summary, Instr(tkt_summary, ' ' 
                                                          , 100 
                                                          -Length( 
                                                          tkt_summary), 1) 
                                                          + 1) 
                               END                         tkt_summary 
                        FROM   (SELECT p1, 
                                       p2, 
                                       p3, 
                                       p4, 
                                       p5, 
                                       p6, 
                                       p7, 
                                       p8, 
                                       p10, 
                                       p11, 
                                       p12, 
                                       13, 
                                       14, 
                                       15, 
                                       16, 
                                       Substr(tkt_summary, 1, CASE 
                                                                WHEN 
                                       Length(tkt_summary) <= 100 
                                                              THEN 
                                                                100 
                                                                ELSE 
                                       Instr(tkt_summary, ' ', 
                                       100 - Length(tkt_summary), 
                                       1) 
                                                              END) p17, 
                                       CASE 
                                         WHEN Length(tkt_summary) <= 100 THEN 
                                         NULL 
                                         ELSE Substr(tkt_summary, 
                                              Instr(tkt_summary, ' ', 100 
                                              -Length( 
                                              tkt_summary), 1) 
                                              + 1) 
                                       END                         tkt_summary 
                                FROM   (SELECT p1, 
                                               p2, 
                                               p3, 
                                               p4, 
                                               p5, 
                                               p6, 
                                               p7, 
                                               p8, 
                                               p10, 
                                               p11, 
                                               p12, 
                                               13, 
                                               14, 
                                               15, 
                                               Substr(tkt_summary, 1, CASE 
                                                                        WHEN 
                                               Length(tkt_summary) <= 100 
                                                                      THEN 
                                                                        100 
                                                                        ELSE 
                                               Instr(tkt_summary, ' ', 
                                               100 - Length(tkt_summary), 
                                               1) 
                                                                      END) p16, 
                                               CASE 
                                                 WHEN Length(tkt_summary) <= 100 
                                               THEN 
                                                 NULL 
                                                 ELSE Substr(tkt_summary, 
                                                      Instr(tkt_summary, ' ', 
                                                      100 
                                                      -Length( 
                                                      tkt_summary), 1) 
                                                      + 1) 
                                               END 
                                               tkt_summary 
                                        FROM   (SELECT p1, 
                                                       p2, 
                                                       p3, 
                                                       p4, 
                                                       p5, 
                                                       p6, 
                                                       p7, 
                                                       p8, 
                                                       p10, 
                                                       p11, 
                                                       p12, 
                                                       13, 
                                                       14, 
                                                       Substr(tkt_summary, 1, 
                                                       CASE 
                                                         WHEN 
                                                       Length(tkt_summary) <= 
                                                       100 
                                                       THEN 
                                                         100 
                                                         ELSE 
                                                       Instr(tkt_summary, ' ', 
                                                       100 - Length(tkt_summary) 
                                                       , 
                                                       1) 
                                                       END) p15, 
                                                       CASE 
                                                         WHEN Length(tkt_summary 
                                                              ) <= 
                                                              100 THEN 
                                                         NULL 
                                                         ELSE Substr(tkt_summary 
                                                              , 
                                                              Instr(tkt_summary, 
                                                              ' ', 
                                                              100 
                                                              -Length( 
                                                              tkt_summary), 1) 
                                                              + 1) 
                                                       END 
                                                       tkt_summary 
                                                FROM 
                                       (SELECT p1, 
                                               p2, 
                                               p3, 
                                               p4, 
                                               p5, 
                                               p6, 
                                               p7, 
                                               p8, 
                                               p10, 
                                               p11, 
                                               p12, 
                                               13, 
                                               Substr(tkt_summary, 1, 
                                               CASE 
                                                 WHEN 
                                               Length(tkt_summary) <= 
                                               100 
                                               THEN 
                                                 100 
                                                 ELSE 
                                               Instr(tkt_summary, ' ', 
                                               100 - Length(tkt_summary 
                                                     ), 
                                               1) 
                                               END) p14, 
                                               CASE 
                                                 WHEN Length( 
                                                 tkt_summary) <= 
                                                      100 THEN 
                                                 NULL 
                                                 ELSE Substr(tkt_summary, 
                                                      Instr(tkt_summary, ' ', 
                                                      100 
                                                      -Length( 
                                                      tkt_summary), 1) 
                                                      + 1) 
                                               END 
                                               tkt_summary 
                                                        FROM 
                                               (SELECT p1, 
                                                       p2, 
                                                       p3, 
                                                       p4, 
                                                       p5, 
                                                       p6, 
                                                       p7, 
                                                       p8, 
                                                       p10, 
                                                       p11, 
                                                       p12, 
                                                       Substr(tkt_summary, 1, 
                                                       CASE 
                                                         WHEN 
                                                       Length(tkt_summary) <= 
                                                       100 
                                                       THEN 
                                                         100 
                                                         ELSE 
                                                       Instr(tkt_summary, ' ', 
                                                       100 - Length(tkt_summary 
                                                             ), 
                                                       1) 
                                                       END) p13, 
                                                       CASE 
                                                         WHEN Length( 
                                                         tkt_summary) <= 
                                                              100 THEN 
                                                         NULL 
                                                         ELSE Substr(tkt_summary 
                                                              , 
                                                              Instr(tkt_summary, 
                                                              ' ', 
                                                              100 
                                                              -Length( 
                                                              tkt_summary), 1) 
                                                              + 1) 
                                                       END 
                                                       tkt_summary 
                                                                FROM 
                                       (SELECT p1, 
                                               p2, 
                                               p3, 
                                               p4, 
                                               p5, 
                                               p6, 
                                               p7, 
                                               p8, 
                                               p10, 
                                               p11, 
                                               Substr(tkt_summary, 1, 
                                               CASE 
                                                 WHEN 
                                               Length(tkt_summary) <= 
                                               100 
                                               THEN 
                                                 100 
                                                 ELSE 
                                               Instr(tkt_summary, ' ', 
                                               100 - Length(tkt_summary 
                                                     ), 
                                               1) 
                                               END) p12, 
                                               CASE 
                                                 WHEN Length( 
                                                 tkt_summary) <= 
                                                      100 THEN 
                                                 NULL 
                                                 ELSE Substr(tkt_summary, 
                                                      Instr(tkt_summary, ' ', 
                                                      100 
                                                      -Length( 
                                                      tkt_summary), 1) 
                                                      + 1) 
                                               END 
                                               tkt_summary 
                                                        FROM 
                                               (SELECT p1, 
                                                       p2, 
                                                       p3, 
                                                       p4, 
                                                       p5, 
                                                       p6, 
                                                       p7, 
                                                       p8, 
                                                       p10, 
                                                       Substr(tkt_summary, 1, 
                                                       CASE 
                                                         WHEN 
                                                       Length(tkt_summary) <= 
                                                       100 
                                                       THEN 
                                                         100 
                                                         ELSE 
                                                       Instr(tkt_summary, ' ', 
                                                       100 - Length(tkt_summary 
                                                             ), 
                                                       1) 
                                                       END) p11, 
                                                       CASE 
                                                         WHEN Length( 
                                                         tkt_summary) <= 
                                                              100 THEN 
                                                         NULL 
                                                         ELSE Substr(tkt_summary 
                                                              , 
                                                              Instr(tkt_summary, 
                                                              ' ', 
                                                              100 
                                                              -Length( 
                                                              tkt_summary), 1) 
                                                              + 1) 
                                                       END 
                                                       tkt_summary 
                                                                FROM 
                                       (SELECT p1, 
                                               p2, 
                                               p3, 
                                               p4, 
                                               p5, 
                                               p6, 
                                               p7, 
                                               p8, 
                                               p9, 
                                               Substr(tkt_summary, 1, 
                                               CASE 
                                                 WHEN 
                                               Length(tkt_summary) <= 
                                               100 
                                               THEN 
                                                 100 
                                                 ELSE 
                                               Instr(tkt_summary, ' ', 
                                               100 - Length(tkt_summary 
                                                     ), 
                                               1) 
                                               END) p10, 
                                               CASE 
                                                 WHEN Length( 
                                                 tkt_summary) <= 
                                                      100 THEN 
                                                 NULL 
                                                 ELSE Substr(tkt_summary, 
                                                      Instr(tkt_summary, ' ', 
                                                      100 
                                                      -Length( 
                                                      tkt_summary), 1) 
                                                      + 1) 
                                               END 
                                               tkt_summary 
                                                        FROM 
                                               (SELECT p1, 
                                                       p2, 
                                                       p3, 
                                                       p4, 
                                                       p5, 
                                                       p6, 
                                                       p7, 
                                                       p8, 
                                                       Substr(tkt_summary, 1, 
                                                       CASE 
                                                         WHEN 
                                                       Length(tkt_summary) <= 
                                                       100 
                                                       THEN 
                                                         100 
                                                         ELSE 
                                                       Instr(tkt_summary, ' ', 
                                                       100 - Length(tkt_summary 
                                                             ), 
                                                       1) 
                                                       END) p9, 
                                                       CASE 
                                                         WHEN Length( 
                                                         tkt_summary) <= 
                                                              100 THEN 
                                                         NULL 
                                                         ELSE Substr(tkt_summary 
                                                              , 
                                                              Instr(tkt_summary, 
                                                              ' ', 
                                                              100 
                                                              -Length( 
                                                              tkt_summary), 1) 
                                                              + 1) 
                                                       END 
                                                       tkt_summary 
                                                                FROM 
                                       (SELECT p1, 
                                               p2, 
                                               p3, 
                                               p4, 
                                               p5, 
                                               p6, 
                                               p7, 
                                               Substr(tkt_summary, 1, 
                                               CASE 
                                                 WHEN 
                                               Length(tkt_summary) <= 
                                               100 
                                               THEN 
                                                 100 
                                                 ELSE 
                                               Instr(tkt_summary, ' ', 
                                               100 - Length(tkt_summary 
                                                     ), 
                                               1) 
                                               END) p8, 
                                               CASE 
                                                 WHEN Length( 
                                                 tkt_summary) <= 
                                                      100 THEN 
                                                 NULL 
                                                 ELSE Substr(tkt_summary, 
                                                      Instr(tkt_summary, ' ', 
                                                      100 
                                                      -Length( 
                                                      tkt_summary), 1) 
                                                      + 1) 
                                               END 
                                               tkt_summary 
                                                        FROM 
                                               (SELECT p1, 
                                                       p2, 
                                                       p3, 
                                                       p4, 
                                                       p5, 
                                                       p6, 
                                                       Substr(tkt_summary, 1, 
                                                       CASE 
                                                         WHEN 
                                                       Length(tkt_summary) <= 
                                                       100 
                                                       THEN 
                                                         100 
                                                         ELSE 
                                                       Instr(tkt_summary, ' ', 
                                                       100 - Length(tkt_summary 
                                                             ), 
                                                       1) 
                                                       END) p7, 
                                                       CASE 
                                                         WHEN Length( 
                                                         tkt_summary) <= 
                                                              100 THEN 
                                                         NULL 
                                                         ELSE Substr(tkt_summary 
                                                              , 
                                                              Instr(tkt_summary, 
                                                              ' ', 
                                                              100 
                                                              -Length( 
                                                              tkt_summary), 1) 
                                                              + 1) 
                                                       END 
                                                       tkt_summary 
                                                                FROM 
                                       (SELECT p1, 
                                               p2, 
                                               p3, 
                                               p4, 
                                               p5, 
                                               Substr(tkt_summary, 1, 
                                               CASE 
                                                 WHEN 
                                               Length(tkt_summary) <= 
                                               100 
                                               THEN 
                                                 100 
                                                 ELSE 
                                               Instr(tkt_summary, ' ', 
                                               100 - Length(tkt_summary 
                                                     ), 
                                               1) 
                                               END) p6, 
                                               CASE 
                                                 WHEN Length( 
                                                 tkt_summary) <= 
                                                      100 THEN 
                                                 NULL 
                                                 ELSE Substr(tkt_summary, 
                                                      Instr(tkt_summary, ' ', 
                                                      100 
                                                      -Length( 
                                                      tkt_summary), 1) 
                                                      + 1) 
                                               END 
                                               tkt_summary 
                                                        FROM 
                                               (SELECT p1, 
                                                       p2, 
                                                       p3, 
                                                       p4, 
                                                       Substr(tkt_summary, 1, 
                                                       CASE 
                                                         WHEN 
                                                       Length(tkt_summary) <= 
                                                       100 
                                                       THEN 
                                                         100 
                                                         ELSE 
                                                       Instr(tkt_summary, ' ', 
                                                       100 - Length(tkt_summary 
                                                             ), 
                                                       1) 
                                                       END) p5, 
                                                       CASE 
                                                         WHEN Length( 
                                                         tkt_summary) <= 
                                                              100 THEN 
                                                         NULL 
                                                         ELSE Substr(tkt_summary 
                                                              , 
                                                              Instr(tkt_summary, 
                                                              ' ', 
                                                              100 
                                                              -Length( 
                                                              tkt_summary), 1) 
                                                              + 1) 
                                                       END 
                                                       tkt_summary 
                                                                FROM 
                                       (SELECT p1, 
                                               p2, 
                                               p3, 
                                               Substr(tkt_summary, 1, 
                                               CASE 
                                                 WHEN 
                                               Length(tkt_summary) <= 
                                               100 
                                               THEN 
                                                 100 
                                                 ELSE 
                                               Instr(tkt_summary, ' ', 
                                               100 - Length(tkt_summary 
                                                     ), 
                                               1) 
                                               END) p4, 
                                               CASE 
                                                 WHEN Length( 
                                                 tkt_summary) <= 
                                                      100 THEN 
                                                 NULL 
                                                 ELSE Substr(tkt_summary, 
                                                      Instr(tkt_summary, ' ', 
                                                      100 
                                                      -Length( 
                                                      tkt_summary), 1) 
                                                      + 1) 
                                               END 
                                               tkt_summary 
                                                        FROM 
                                               (SELECT p1, 
                                                       p2, 
                                                       Substr(tkt_summary, 1, 
                                                       CASE 
                                                         WHEN 
                                                       Length(tkt_summary) <= 
                                                       100 
                                                       THEN 
                                                         100 
                                                         ELSE 
                                                       Instr(tkt_summary, ' ', 
                                                       100 - Length(tkt_summary 
                                                             ), 
                                                       1) 
                                                       END) p3, 
                                                       CASE 
                                                         WHEN Length( 
                                                         tkt_summary) <= 
                                                              100 THEN 
                                                         NULL 
                                                         ELSE Substr(tkt_summary 
                                                              , 
                                                              Instr(tkt_summary, 
                                                              ' ', 
                                                              100 
                                                              -Length( 
                                                              tkt_summary), 1) 
                                                              + 1) 
                                                       END 
                                                       tkt_summary 
                                                                FROM 
                                       (SELECT p1, 
                                               Substr(tkt_summary, 1, 
                                               CASE 
                                                 WHEN 
                                               Length(tkt_summary) <= 
                                               100 
                                               THEN 
                                                 100 
                                                 ELSE 
                                               Instr(tkt_summary, ' ', 
                                               100 - Length(tkt_summary 
                                                     ), 
                                               1) 
                                               END) p2, 
                                               CASE 
                                                 WHEN Length( 
                                                 tkt_summary) <= 
                                                      100 THEN 
                                                 NULL 
                                                 ELSE Substr(tkt_summary, 
                                                      Instr(tkt_summary, ' ', 
                                                      100 
                                                      -Length( 
                                                      tkt_summary), 1) 
                                                      + 1) 
                                               END 
                                               tkt_summary 
                                                        FROM 
                                               (SELECT Substr(tkt_summary, 1, 
                                                       CASE 
                                                         WHEN 
                                                       Length(tkt_summary) <= 
                                                       100 
                                                       THEN 
                                                         100 
                                                         ELSE 
                                                       Instr(tkt_summary, ' ', 
                                                       100 - Length(tkt_summary 
                                                             ), 
                                                       1) 
                                                       END) p1, 
                                                       CASE 
                                                         WHEN Length( 
                                                         tkt_summary) <= 
                                                              100 THEN 
                                                         NULL 
                                                         ELSE Substr(tkt_summary 
                                                              , 
                                                              Instr(tkt_summary, 
                                                              ' ', 
                                                              100 
                                                              -Length( 
                                                              tkt_summary), 1) 
                                                              + 1) 
                                                       END 
                                                       tkt_summary 
                                                                FROM   ticket))) 
                                       ))))))) 
                                                               )))))))))); 

Open in new window

0
johnsoneSenior Oracle DBACommented:
If you are actually going to consider the method that I posted, I highly recommend that you change it to be 200 characters and cut the number of columns back to 11.  That makes the inefficiency at least bearable.

To make the change, all you have to do is change 100 to 200 everywhere, then take selects off the front and parenthesis off the end.
0
flow01Commented:
Or a basic working example  based on a pl/sql function
What should happen when there are more then 50 continuous characters  : split on 50 anyway or accept a piece > then 50 characters ?  I choose keep, but above 250 characters i did not test.

i added the source of split_text  separatly
you can comment the dbms_output

test run

select  split_text(text)
 from user_source where rownum < 50
 and length(text) > 50
  order by length(text) desc
split-text.fnc.txt
0
Maliki HassaniAuthor Commented:
great that's awesome yeah that's a lot of coding but it seems to be the only way that it can work I will test this code out when I return tomorrow thank you for your assistance
0
slightwv (䄆 Netminder) Commented:
>>but it seems to be the only way that it can work

Probably not the only way.  If can probably be much more efficient if you can create a function in the database.
0
johnsoneSenior Oracle DBACommented:
I was trying to do a purely SQL way.  As I said, it is horribly inefficient.  I wasn't sure if a function was a viable solution.  Sometimes adding functions isn't allowed.

You could turn the same basic logic into a function.  That should be much more efficient.
0
Maliki HassaniAuthor Commented:
Yes, i could add as a function. Udf?
0
johnsoneSenior Oracle DBACommented:
Not extensively tested, but here is a function:
CREATE OR replace FUNCTION Str_break (v_str VARCHAR2) 
RETURN VARCHAR2 
AS 
  v_breakstr VARCHAR2(2000); 
  v_return   VARCHAR2(4000) := NULL; 
  v_maxlen   NUMBER := 50; 
  v_breakpos NUMBER; 
BEGIN 
    v_breakstr := v_str; 

    LOOP 
        IF Length(v_breakstr) <= 50 THEN 
          IF v_return IS NULL THEN 
            v_return := v_breakstr; 
          ELSE 
            v_return := v_return 
                        || Chr(10) 
                        || v_breakstr; 
          END IF; 

          exit; 
        END IF; 

        SELECT Instr(v_breakstr, ' ', v_maxlen - Length(v_breakstr)) 
        INTO   v_breakpos 
        FROM   dual; 

        IF v_breakpos = 0 THEN 
          v_breakpos := Length(v_breakstr); 
        END IF; 

        IF v_return IS NOT NULL THEN 
          v_return := v_return 
                      || Chr(10); 
        END IF; 

        v_return := v_return 
                    || Substr(v_breakstr, 1, v_breakpos); 

        v_breakstr := Substr(v_breakstr, v_breakpos + 1); 
    END LOOP; 

    RETURN( v_return ); 
END; 

/ 

Open in new window

Seems to work in the few test cases that I ran at it.

I see that there was another function already posted, but I couldn't follow it.  The for loops with fixed iterations didn't seem to make sense to me.  This way seems simpler to me.  Keep iterating through the string and breaking it apart.

To call it, just use it in a select statement like this:

select str_break(tkt_summary) from mytab;

Definitely much more efficient than the recursive SQL way.
0
Maliki HassaniAuthor Commented:
Thank you for taking the time to do this.  I will run it tomorrow morning!
0
flow01Commented:
@johnsone :  the only sense in the fixed iterations is to avoid a loop:   as long as not fully tested i rather get a wrong result then no result at all.
0
johnsoneSenior Oracle DBACommented:
@flow01:  Not saying what you posted is wrong.  I had trouble following it.  It may work just fine.
0
Maliki HassaniAuthor Commented:
> Script lines: 1-3 --------------------------
 ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "BENCO.STR_BREAK", line 33
 Script line 1, statement line 1, column 7 


Any idea why this error is occurring?
0
Maliki HassaniAuthor Commented:
Seems to work when a run the query for a small amount of records.  When my where clause is pulling a 100+ tickets it errors.  Still reviewing.
0
johnsoneSenior Oracle DBACommented:
Number of records shouldn't matter.  It is one particular record that is causing the issue.  Bump up the size of v_breakstr to 4000.
0
Maliki HassaniAuthor Commented:
Yeah, I think that is the issue.. Narrowing down the record that is causing this. thanks
0
johnsoneSenior Oracle DBACommented:
Actually, based on where the error is, I don't think that is the issue.  I think it is an issue where you have a "word" that is greater than 50 characters.  Change this:
        IF v_breakpos = 0 THEN 
          v_breakpos := Length(v_breakstr); 
        END IF; 

Open in new window

to this:
        IF v_breakpos = 0 THEN 
          v_breakpos := v_maxlen; 
        END IF; 

Open in new window

That occurs around line 28.  I think it can get into an infinite loop if there is a "word" greater than 50 characters.  This change should fix that.
0
Maliki HassaniAuthor Commented:
So that fixed the error message, but now it is taking a long time to run which seems like an endless loop.  Trying to find the record that is causing this again.
0
Maliki HassaniAuthor Commented:
Issue is that there is a null in the summary
0
johnsoneSenior Oracle DBACommented:
Nulls are causing the issue.  Try this:

CREATE OR replace FUNCTION Str_break (v_str VARCHAR2) 
RETURN VARCHAR2 
AS 
  v_breakstr VARCHAR2(2000); 
  v_return   VARCHAR2(4000) := NULL; 
  v_maxlen   NUMBER := 50; 
  v_breakpos NUMBER; 
BEGIN 
    IF ( v_str IS NULL ) THEN 
      RETURN( NULL ); 
    END IF; 

    v_breakstr := v_str; 

    LOOP 
        IF Length(v_breakstr) <= 50 THEN 
          IF v_return IS NULL THEN 
            v_return := v_breakstr; 
          ELSE 
            v_return := v_return 
                        || Chr(10) 
                        || v_breakstr; 
          END IF; 

          exit; 
        END IF; 

        SELECT Instr(v_breakstr, ' ', v_maxlen - Length(v_breakstr)) 
        INTO   v_breakpos 
        FROM   dual; 

        IF v_breakpos = 0 THEN 
          v_breakpos := v_maxlen; 
        END IF; 

        IF v_return IS NOT NULL THEN 
          v_return := v_return 
                      || Chr(10); 
        END IF; 

        v_return := v_return 
                    || Substr(v_breakstr, 1, v_breakpos); 

        v_breakstr := Substr(v_breakstr, v_breakpos + 1); 
    END LOOP; 

    RETURN( v_return ); 
END; 

/ 

Open in new window


I tested it with a string shorter than 50 characters, one that is about  200 characters, one that is null and one that has a word over 50 characters.  All seem to be working OK.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Maliki HassaniAuthor Commented:
Bingo got it to work!
0
Maliki HassaniAuthor Commented:
Thanks everyone for the assistance!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.