How to split varchar column in Oracle in three columns

Question

I have an address field that can contain 120 characters and I need to split it into three different columns with a length of 40 characters.

Example:

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

Conclusion: 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) data type.

So the 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 contain up to 40 characters and must be a whole word, it cannot be truncated in half and left meaningless.

I am using oracle 11i database.

Answer 1

You can 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; 

What 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 these partial values ​​to columns, which I think is your final goal, for example. 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 script .

It may be worth noting that if the length of street_address approaches 120 characters, it may not fit neatly into 3 40-char blocks - you lose some characters depending on the length of the word enclosed in the next 'line'. This approach will generate more than 3 lines, but the view uses only the first three, so you may lose the end of the address. You might want the fields to be longer or have address_4 for these situations ...

Answer 2

It's pretty “quick and dirty,” but I think it gives the right results.
I used a pipeline table, but you can probably do this without it ...

Here is the sqlfiddle daemon

 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 request:

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