如何在三列中拆分Oracle中的varchar列

例：

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

输出：
123 Main St North Pole Factory 44,靠近后门,过马路,左转,继续行走.

我需要将此地址拆分为address_1
地址2
和address_3.

所有三个地址都是varchar(40)数据类型.

所以结果应该是这样的：

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.

请注意,每个地址字段最多可以包含40个字符,并且必须是整个字,它不能被截断一半而且没有意义.

我正在使用oracle 11i数据库.

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,40),' ',-1,1)) end
        as part_address,case when length(remaining) <= 40 then null else
      substr(remaining,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.

SQL Fiddle demo有两个地址.

您也可以将这些部分值转换为列,我认为这是您的最终目标,例如作为一种观点：

create or replace view v_address as
with cte (street_address,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.

值得注意的是,如果street_address长度接近120个字符,它可能不适合整齐地放入3个40个字符块中 – 你会丢失一些字符,具体取决于包含在下一个“行”中的字的长度.此方法将生成3行以上,但视图仅使用前三行,因此您可能会丢失地址的末尾.您可能希望将字段设置得更长,或者为这些情况设置address_4 …

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!