Hi
I need to split a string dynamically into 2 parts each of maximum 25characters but if 25th character is not the right break (space) it should consider the last space.
example :'1100 Crescent PkWay Apartment 101 suite 200'
30 characters would be "1100 Crescent PkWay Apart" then I need it as
first part : 1100 Crescent PkWay
second : Apartment 101 suite 200
i got the working example from the below 'Ask Tom' link but when i try to incoporate it into a select query the first value keeps repeating for all rows.
https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9541334200346907017
Below is my code example
--create table
CREATE TABLE TEST_ADDRESS(ADDRESS VARCHAR2(100));
--add data
INSERT INTO TEST_ADDRESS VALUES ('1411 FOREST LANE FAIRBORN OH 45324');
INSERT INTO TEST_ADDRESS VALUES ('1100 CRESCENT PKWAY APARTMENT 101 SUITE 200');
--select query
SELECT (SELECT LISTAGG(W, ' ') WITHIN GROUP(ORDER BY WORD#) SPLIT_STRS
FROM (SELECT *
FROM (SELECT REGEXP_SUBSTR(A.ADDRESS, '[^ ]+', 1, LEVEL) W
FROM DUAL
CONNECT BY REGEXP_SUBSTR(A.ADDRESS, '[^ ]+', 1, LEVEL) IS NOT NULL)
WORDS MATCH\_RECOGNIZE(MEASURES MATCH\_NUMBER() GRP, COUNT(TEXT.\*) WORD# ALL ROWS PER MATCH PATTERN(INIT TEXT \*) DEFINE TEXT AS SUM(LENGTH(W)) + COUNT(TEXT.\*) \<= 25)
) GRPS
GROUP BY GRP
ORDER BY GRP
FETCH FIRST 1 ROWS ONLY) FIRST_PART FROM TEST_ADDRESS A;
My Output
