oracle - 如何将 Oracle 中的 varchar 列拆分为三列

Question

我有一个可以容纳 120 个字符的地址字段，需要将它分成三个不同的列，每列 40 个字符长。

例子:

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

输出: 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)数据类型。

所以结果应该是这样的:

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 数据库。

Answer 1

您可以使用递归子查询分解(递归 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;

你的数据给出了:

      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, 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 .

可能值得注意的是，如果 street_address长度接近 120 个字符，它可能无法整齐地放入 3 个 40 个字符的块中 - 根据包装到下一个“行”的单词的长度，您会丢失一些字符。这种方法会生成超过 3 行，但 View 只使用前三行，因此您可能会丢失地址的结尾。您可能希望使字段更长，或者使用 address_4对于那些情况...