oracle中怎么拆分一个列,如何在三列中拆分Oracle中的varchar列

您可以使用递归子查询因子(递归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,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.

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

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;

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