The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

Can I use sa_split_list to split a row on first space nearest 20 character?


Test this row and try to split the row after 20 characters

Should be:
1.Test this row and
2.try to split the row
3.after 20 characters

asked 15 Mar '16, 12:01

Rolle's gravatar image

accept rate: 0%

edited 15 Mar '16, 12:04

A sample would help to clarify your needs - is that just one string to split or a column of a result set?

I guess LOCATE in conjunction with SUBSTR and LEFT is more helpful here, such as

   SET str = 'ThisLongLineHas Three Spaces InIt.';
   SET pos = LOCATE(LEFT(str, 20), ' ');
   SELECT str, pos,
      SUBSTR( str, 1, pos -1 ) AS str1,
      SUBSTR( str, pos +1 ) AS str2;


'ThisLongLineHas Three Spaces InIt.',16,'ThisLongLineHas','Three Spaces InIt.'

(15 Mar '16, 12:24) Volker Barth

It's a column of a resultset. There, the length can vary and I need to split the rows if they are more than 20 characters. Split on the first suitable space so close to 20 characters as possible.

(15 Mar '16, 15:20) Rolle
Replies hidden

So this is more like an attempt to wrap words, i.e. the whole text has to be split into up to 20 chars for each resulting "line"?

FWIW: Note, you can use LOCATE() with a third parameter -1 to search from the end (whereas I had thought you would search for the first space) and could use that in a loop until the rest is shorter than 20 chars.

Another attempt would be to use sa_split_list to break after each space and then re-concat the resulting "words" until they would exceed 20 chars together.

(15 Mar '16, 16:49) Volker Barth

Thanks Volker.

Can u show me examples with sa_split_list and Locate()?

(15 Mar '16, 17:16) Rolle

Here's a sample using a stored procedure to split a string into according pieces and some test code. Note, it will only break at spaces, not other whitespace or punctuation (which might be useful), and it will leave longer "words" unchanged, so it does not guarantee that resulting lines are limited to 20 chars...

-- Some test data
create table T_Test (
   line_no int not null default autoincrement primary key,
   line    long varchar not null
insert T_Test (line) values 
   ('Test this row and try to split the row after 20 characters'),
   ('ThisLongLineHas Three Spaces InIt.'),
   ('Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum.');

-- split "str" at "delim" boundaries into lines with maximum "max_line_len" length
create or replace procedure STP_LineBreaker(in str long varchar, in max_line_len int, in delim char(1) default ' ')
result (line_no int, line long varchar)
   declare pos int;
   declare local temporary table LT_WrappedLine (
      line_no int not null default autoincrement primary key,
      line    long varchar not null
   ) not transactional;

   -- use locate with negative index (search from end) and use left with one more char than "max_line_len"
   -- (if you want at maximum 20 char, it's allright if the delim is at position 21)
   set pos = locate(left(str, max_line_len + 1), delim, -1);
   while len(str) > max_line_len and pos > 0 loop
      insert LT_WrappedLine (line) select substr(str, 1, pos - 1);
      set str = substr(str, pos + 1);
      set pos = locate(left(str, max_line_len + 1), delim, -1);
   end loop;
   -- insert the remaining part
   insert LT_WrappedLine (line) select str;

   select line_no, line from LT_WrappedLine order by 1;  

-- test call with your sample line (and dispalying the resulting lines's length):
select *, len(line)
from STP_LineBreaker('Test this row and try to split the row after 20 characters', 20);

This returns:

1,Test this row and,17
2,try to split the row,20
3,after 20 characters,19

-- test call over the test table with the handy CROSS APPLY operator:
select T_Test.line_no, sp.*, len(sp.line)
from T_Test cross apply STP_LineBreaker(line, 20, ' ') sp
order by 1, 2

This returns:

1,1,Test this row and,17
1,2,try to split the row,20
1,3,after 20 characters,19
2,2,Three Spaces InIt.,18
4,1,Lorem ipsum dolor,17
4,2,sit amet, consetetur,20
4,3,sadipscing elitr,,17
4,4,sed diam nonumy,15
4,5,eirmod tempor,13
4,6,invidunt ut labore,18
4,7,et dolore magna,15
4,8,aliquyam erat, sed,18
4,9,diam voluptua. At,17
4,10,vero eos et accusam,19
4,11,et justo duo dolores,20
4,12,et ea rebum.,12

As you can see, the original line 3 is not split into 20 chars or less as it has no spaces within. Also note that the "delim" character is omitted from the result. That may be allright with spaces but may not be adequate for other characters. But it shouldn't be too difficult to modify the procedure to add the "delim" to the result set - you would then choose "select substr(str, 1, pos);" but use left without the additional "+ 1" increment.

permanent link

answered 16 Mar '16, 03:22

Volker%20Barth's gravatar image

Volker Barth
accept rate: 33%

edited 16 Mar '16, 03:28

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 15 Mar '16, 12:01

question was seen: 408 times

last updated: 16 Mar '16, 03:28