PL/SQL Break Column based on Characters

Question

Column name: Full_address

"B230 E Campus; 3405; Mail Code: 600 W 110th Street; Dallas NY 10027; United States"

Hello, I have the above as an example and I would like to break it up into different columns based on the characters or just the semi columns (;)

I tried all sorts of line but none worked. Left/Right and CHARINDEX do not work with my version of PL/SQL Oracle.

 regexp_substr(Full_address,'[^[:print:]]|,/')
  SUBSTR (t.address_one_line, INSTR('_', Full_address)-10),
  regexp_substr(upper(Full_address), '(^\w+)-', ,, 10, 'i', 10)



I want 5 columns 
B230 E Campus
3405  
Mail Code: 600 W 110th Street
Dallas NY 10027
United States

Answer 1

Here's one way. The with clause just sets up test data. Basically select the nth instance of a set of characters followed by a semi-colon then zero or more spaces (consumes the spaces, thus saving a call to TRIM()) or the end of the line.

with tbl(str) as (
  select 'B230 E Campus; 3405; Mail Code: 600 W 110th Street; Dallas NY 10027; United States'
    from dual
)
select regexp_substr(str, '(.*?)(; *|$)', 1, 1, null, 1) as col_1,
       regexp_substr(str, '(.*?)(; *|$)', 1, 2, null, 1) as col_2,
       regexp_substr(str, '(.*?)(; *|$)', 1, 3, null, 1) as col_3,
       regexp_substr(str, '(.*?)(; *|$)', 1, 4, null, 1) as col_4,
       regexp_substr(str, '(.*?)(; *|$)', 1, 5, null, 1) as col_5
from tbl;


COL_1         COL_2 COL_3                         COL_4           COL_5        
------------- ----- ----------------------------- --------------- -------------
B230 E Campus 3405  Mail Code: 600 W 110th Street Dallas NY 10027 United States