In PL-SQL, I want to concatenate two strings taken from 2 columns (address line 1, address line 2, max 45 characters each) into 3 strings (address line 1, address line 2, address line 3, maximum 34 characters each) based on the condition that no word should cut in the middle. for example:
If address line 1 contains:
1, abc park, def chowk, ghi marg c-123 street
and address line 2 contains:
city mumbai, pin - 435353
Combined with numbering to show where 34 characters falls:
1111111111222222222233333 1111111111222222222233333
12345678901234567890123456789012341234567890123456789012345678901234123
1, abc park, def chowk, ghi marg c-123 street city mumbai, pin - 435353
The result should be like this
Add1 (max 34 char):
1, abc park, def chowk, ghi marg
Add2 (max 34 char):
c-123 street city mumbai,
Add3 (max 34 char):
pin - 435353
REGEXP_SUBSTR() can be used to solve this for you. As I said in my comment to your original post, your text says don't break in a word but your example shows ADD3 breaking after the last comma/space, not just space so you need to define your rule further (Maybe when it's the last section after the last comma only?). Anyway, sticking with what you wrote, the regex gives either the 1st, 2nd or 3rd occurrence of up to 34 characters that are followed by a character that is not a whitespace, followed by a whitespace character or the end of the line.
SQL> with tbl(addr) as (
select '1, abc park, def chowk, ghi marg c-123 street city mumbai, pin - 435353'
from dual
)
select regexp_substr(addr, '(.{0,34}\S)(\s|$)', 1, 1) add1,
regexp_substr(addr, '(.{0,34}\S)(\s|$)', 1, 2) add2,
regexp_substr(addr, '(.{0,34}\S)(\s|$)', 1, 3) add3
from tbl;
ADD1 ADD2 ADD3
--------------------------------- -------------------------------- ------
1, abc park, def chowk, ghi marg c-123 street city mumbai, pin - 435353
SQL>