如何将Oracle中的varchar列拆分为三列
•浏览 1
How to split varchar column in Oracle in three columns
我有一个可以容纳 120 个字符的地址字段，需要将它分成三个不同的列，每列 40 个字符长。
示例：
Table name: Address
Column name: Street_Address
Select Street_Address * from AddressAddress_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.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; 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.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;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;
/select parts.*, length(PHRASE1), length(PHRASE2), length(PHRASE3)
from t1, table(split_string(t1.id, t1.adr)) parts输出：
123 Main St North Pole Factory 44, near the rear entrance cross the street and turn left and keep walking straight.
我需要把这个地址拆分成 address_1
address_2
和 address_3.
所有三个地址都是 varchar(40) 数据类型。
所以结果应该是这样的：
Table name: Address
Column name: Street_Address
Select Street_Address * from AddressAddress_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.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; 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.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;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;
/select parts.*, length(PHRASE1), length(PHRASE2), length(PHRASE3)
from t1, table(split_string(t1.id, t1.adr)) parts请注意，每个地址字段最多可以占用 40 个字符，并且必须是整个单词，它不能被截断一半而没有意义。
我正在使用 oracle 11i 数据库。
您可以使用递归子查询分解(递归 CTE)：
Table name: Address
Column name: Street_Address
Select Street_Address * from AddressAddress_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.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; 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.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;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;
/select parts.*, length(PHRASE1), length(PHRASE2), length(PHRASE3)
from t1, table(split_string(t1.id, t1.adr)) parts你的数据给出了：
Table name: Address
Column name: Street_Address
Select Street_Address * from AddressAddress_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.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; 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.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;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;
/select parts.*, length(PHRASE1), length(PHRASE2), length(PHRASE3)
from t1, table(split_string(t1.id, t1.adr)) parts带有两个地址的 SQL Fiddle 演示。
您还可以将这些部分值转换为列，我认为这是您的最终目标，例如作为一个视图：
Table name: Address
Column name: Street_Address
Select Street_Address * from AddressAddress_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.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; 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.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;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;
/select parts.*, length(PHRASE1), length(PHRASE2), length(PHRASE3)
from t1, table(split_string(t1.id, t1.adr)) parts另一个 SQL 小提琴。
值得注意的是，如果 street_address 的长度接近 120 个字符，它可能无法整齐地放入 3 个 40 字符的块中 - 您会丢失一些字符，具体取决于包裹到下一行的单词的长度'。这种方法会生成多于 3 行，但视图只使用前三行，因此您可能会丢失地址的结尾。您可能希望使字段更长，或者在这些情况下使用 address_4...
这相当"又快又脏"，但我认为它给出了正确的结果。
我使用了流水线表，但可能没有它也可以完成...
这是一个 sqlfiddle 演示
Table name: Address
Column name: Street_Address
Select Street_Address * from AddressAddress_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.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; 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.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;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;
/select parts.*, length(PHRASE1), length(PHRASE2), length(PHRASE3)
from t1, table(split_string(t1.id, t1.adr)) parts然后查??询：
Table name: Address
Column name: Street_Address
Select Street_Address * from AddressAddress_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.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; 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.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;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;
/select parts.*, length(PHRASE1), length(PHRASE2), length(PHRASE3)
from t1, table(split_string(t1.id, t1.adr)) parts