How to split varchar column in Oracle in three columns

Question

I have address field which can hold 120 characters and need to split it into three different columns 40 charcaters long each.

Example:

Table name: Address 
Column name: Street_Address
Select Street_Address  * from Address

Output: 123 Main St North Pole Factory 44, near the rear entrance cross the street and turn left and keep walking straight.

I need to split this address into address_1 address_2 and address_3.

All three addresses are varchar(40) datatype.

So result should be something like this:

Address_1
152 Main st North Pole Factory 44, near 

Address_2
the rear entrance cross the street and

Address_3
turn left and keep walking straight.

Please note that each address field can take up to 40 characters and has to be the whole word, it can't be truncated in half and left meaningless.

I am using oracle 11i database.

Answer 1

You could use recursive subquery factoring (recursive CTE):

with s (street_address, line, part_address, remaining) as (
  select street_address, 0 as line,
    null as part_address, street_address as remaining
  from address
  union all
  select street_address, line + 1 as line,
    case when length(remaining) <= 40 then remaining else
      substr(remaining, 1, instr(substr(remaining, 1, 40), ' ', -1, 1)) end
        as part_address,
    case when length(remaining) <= 40 then null else
      substr(remaining, instr(substr(remaining, 1, 40), ' ', -1, 1) + 1) end
        as remaining
  from s
)
cycle remaining set is_cycle to 'Y' default 'N'
select line, part_address
from s
where part_address is not null
order by street_address, line;

Which with your data gives:

      LINE PART_ADDRESS                           
---------- ----------------------------------------
         1 152 Main st North Pole Factory 44, near  
         2 the rear entrance cross the street and   
         3 turn left and keep walking straight.     

SQL Fiddle demo with two addresses.

You can also convert those partial values to columns, which I think is your end goal, e.g. as a view:

create or replace view v_address as
with cte (street_address, line, part_address, remaining) as (
  select street_address, 0 as line,
    null as part_address, street_address as remaining
  from address
  union all
  select street_address, line + 1 as line,
    case when length(remaining) <= 40 then remaining else
      substr(remaining, 1, instr(substr(remaining, 1, 40), ' ', -1, 1)) end
        as part_address,
    case when length(remaining) <= 40 then null else
      substr(remaining, instr(substr(remaining, 1, 40), ' ', -1, 1) + 1) end
        as remaining
  from cte
)
cycle remaining set is_cycle to 'Y' default 'N'
select street_address,
  cast (max(case when line = 1 then part_address end) as varchar2(40))
    as address_1,
  cast (max(case when line = 2 then part_address end) as varchar2(40))
    as address_2,
  cast (max(case when line = 3 then part_address end) as varchar2(40))
    as address_3
from cte
where part_address is not null
group by street_address;

Another SQL Fiddle.

It may be worth noting that if the street_address length gets close to 120 chars, it might not fit neatly into 3 40-char blocks - you lose some chars depending on the length of the word wrapped to the next 'line'. This approach will generate more than 3 lines, but the view is only using the first three, so you might lose the end of the address. You might want to make the fields longer, or have an address_4 for those situations...

Answer 2

This quite "quick and dirty" but I think it gives the correct results.
I used a pipelined table but probably it can be done without it...

Here is a sqlfiddle demo

create table t1(id number, adr varchar2(120))
/
insert into t1 values(1, '152 Main st North Pole Factory 44, near the rear entrance cross the street and turn left and keep walking straight.')
/
insert into t1 values(2, '122 Main st Pole Factory 44, near the rear entrance cross the street and turn left and keep walking straight. asdsa')
/

create or replace type t is object(id number, phrase1 varchar2(40), phrase2 varchar2(40), phrase3 varchar2(40))
/
create or replace type t_tab as table of t
/

create or replace function split_string(id number, str in varchar2) return t_tab
  pipelined is

  v_token   varchar2(40);
  v_token_i number := 0;
  v_cur_len number := 0;
  v_res_str varchar2(121) := str || ' ';
  v_p1      varchar2(40);
  v_p2      varchar2(40);
  v_p3      varchar2(40);
  v_p_i     number := 1;

begin

  v_token_i := instr(v_res_str, ' ');

  while v_token_i > 0 loop

    v_token := substr(v_res_str, 1, v_token_i - 1);

      if v_cur_len + length(v_token) < 40 then

        if v_p_i = 1 then 
          v_p1 := v_p1 || ' ' || v_token;
        elsif v_p_i = 2 then 
          v_p2 := v_p2 || ' ' || v_token;
        elsif v_p_i = 3 then 
          v_p3 := v_p3 || ' ' || v_token;
        end if;

        v_cur_len := v_cur_len + length(v_token) +1;
     else
        v_p_i := v_p_i + 1;

        if v_p_i = 2 then 
          v_p2 := v_p2 || ' ' || v_token;
        elsif v_p_i = 3 then 
          v_p3 := v_p3 || ' ' || v_token;
        end if;

        v_cur_len := length(v_token);

     end if;

     v_res_str := substr(v_res_str, v_token_i + 1);
     v_token_i := instr(v_res_str, ' ');

   end loop;

   pipe row(t(id, v_p1, v_p2, v_p3));
   return;
end split_string;
/

And the query:

select parts.*, length(PHRASE1), length(PHRASE2), length(PHRASE3)
from t1, table(split_string(t1.id, t1.adr)) parts