sa_split_list after 20 characters

[former_SQLA_member1694869]

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

Ex:

This: 
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

[VolkerBarth]

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

BEGIN
   DECLARE str LONG VARCHAR;
   DECLARE pos INT;
   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;
END;

returns

str,pos,str1,str2
'ThisLongLineHas Three Spaces InIt.',16,'ThisLongLineHas','Three Spaces InIt.'

[former_SQLA_member1694869]

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.

[VolkerBarth]

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.

[former_SQLA_member1694869]

Thanks Volker.

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

[VolkerBarth · Accepted solution]

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.'),
   ('This.Long.Line.Has.No.Spaces.In.It,Which-Is-A-Pity.'),
   ('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)
begin
   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;  
end;

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

line_no,line,len(STP_LineBreaker.line)
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:

line_no,line_no,line,len(sp.line)
1,1,Test this row and,17
1,2,try to split the row,20
1,3,after 20 characters,19
2,1,ThisLongLineHas,15
2,2,Three Spaces InIt.,18
3,1,This.Long.Line.Has.No.Spaces.In.It,Which-Is-A-Pity.,51
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.