in oracle sql developers segment data into different columns

I believe you are looking for something like this. Expects 3 columns and allows for NULL elements.

-- table_1 just sets up the test data
WITH table_1(message) AS (
  SELECT 'John Doe'||CHR(13)||CHR(10)||'555-555-5555'||CHR(13)||CHR(10)||'John This is a test message' FROM dual UNION ALL
  SELECT 'Jane Smith'||CHR(13)||CHR(10)||'555-555-1234'||CHR(13)||CHR(10)||'Jane This is a test message' FROM dual UNION ALL
  SELECT 'Lance Link'||CHR(13)||CHR(10)||'555-555-1212'||CHR(13)||CHR(10)||'Lance This is a test message' FROM dual
)
SELECT message,
       REGEXP_SUBSTR(message, '(.*?)('||CHR(13)||CHR(10)||'|$)', 1, LEVEL,   NULL, 1) column_1,
       REGEXP_SUBSTR(message, '(.*?)('||CHR(13)||CHR(10)||'|$)', 1, LEVEL+1, NULL, 1) column_2,
       REGEXP_SUBSTR(message, '(.*?)('||CHR(13)||CHR(10)||'|$)', 1, LEVEL+2, NULL, 1) column_3
FROM table_1
CONNECT BY LEVEL <= REGEXP_COUNT(message, 'CHR(13)')+1
  AND PRIOR message = message
  AND PRIOR SYS_GUID() IS NOT NULL;


 MESSAGE                        COLUMN_1        COLUMN_2        COLUMN_3                      
------------------------------ --------------- --------------- ------------------------------
Jane Smith
555-555-1234
Jane This is a test message   Jane Smith      555-555-1234    Jane This is a test message   
                                                                                             
                                                                                                    
John Doe
555-555-5555
John This is a test message   John Doe        555-555-5555    John This is a test message   
                                                                                             
                                                                                                    
Lance Link
555-555-1212
Lance This is a test message  Lance Link      555-555-1212    Lance This is a test message  
                                                                                             
                                                                                                    

3 rows selected.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top