草庐IT

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

codeneng 2023-03-28 原文

How to split varchar column in Oracle in three columns

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

示例：

1
2
3
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) 数据类型。

所以结果应该是这样的：

1
2
3
4
5
6
7
8
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 数据库。

  • 您将在哪里显示这些列？为什么不在应用程序级别拆分它？

您可以使用递归子查询分解(递归 CTE)：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;

你的数据给出了：

1
2
3
4
5
      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 演示。

您还可以将这些部分值转换为列，我认为这是您的最终目标，例如作为一个视图：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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;

另一个 SQL 小提琴。

值得注意的是，如果 street_address 的长度接近 120 个字符，它可能无法整齐地放入 3 个 40 字符的块中 - 您会丢失一些字符，具体取决于包裹到下一行的单词的长度'。这种方法会生成多于 3 行，但视图只使用前三行，因此您可能会丢失地址的结尾。您可能希望使字段更长，或者在这些情况下使用 address_4...

这相当"又快又脏"，但我认为它给出了正确的结果。
我使用了流水线表，但可能没有它也可以完成...

这是一个 sqlfiddle 演示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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;
/

然后查??询：

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

  • @D.L，哎呀，忘记了最后一个单词的情况(没有空格)更新了我的答案。 (顺便说一句，还有另一种解决方法 - 你可以初始化 v_res_str varchar2(120) := str || ' ';
  • @D.L，你说的即时是什么意思？您可以使用建议的查询作为视图(可能没有长度字段...)
  • @D.L，我仍然没有看到问题 - 您也可以添加任何其他列。请参阅此示例 sqlfiddle.com/#!4/eeb18/1
  • @D.L，嗯......好吧，当地址的长度为119时，它会发生......好吧，那么让我们使用第二种方法来修复"最后一个单词问题"，我会更新我的答案。顺便说一句，请注意，正如亚历克斯普尔在他的回答中所说的那样，您可能会失去一些词
  • @D.L.，不，varchar2 可以容纳超过 120 个，您不需要 clob。你能显示导致错误的输入吗？另一件事，你增加了多少尺寸？你对 TYPE 和函数变量都做了吗？

varcharOraclenbspbrsectionmultiple-columnsoraclesplit

有关如何将Oracle中的varchar列拆分为三列的更多相关文章

  1. ruby - 如何使用 Nokogiri 的 xpath 和 at_xpath 方法 - 2

    我正在学习如何使用Nokogiri，根据这段代码我遇到了一些问题:require'rubygems'require'mechanize'post_agent=WWW::Mechanize.newpost_page=post_agent.get('http://www.vbulletin.org/forum/showthread.php?t=230708')puts"\nabsolutepathwithtbodygivesnil"putspost_page.parser.xpath('/html/body/div/div/div/div/div/table/tbody/tr/td/div

  2. ruby - 如何从 ruby​​ 中的字符串运行任意对象方法？ - 2

    总的来说，我对ruby​​还比较陌生，我正在为我正在创建的对象编写一些rspec测试用例。许多测试用例都非常基础，我只是想确保正确填充和返回值。我想知道是否有办法使用循环结构来执行此操作。不必为我要测试的每个方法都设置一个assertEquals。例如:describeitem,"TestingtheItem"doit"willhaveanullvaluetostart"doitem=Item.new#HereIcoulddotheitem.name.shouldbe_nil#thenIcoulddoitem.category.shouldbe_nilendend但我想要一些方法来使用

  3. ruby - 其他文件中的 Rake 任务 - 2

    我试图在一个项目中使用rake，如果我把所有东西都放到Rakefile中，它会很大并且很难读取/找到东西，所以我试着将每个命名空间放在lib/rake中它自己的文件中，我添加了这个到我的rake文件的顶部:Dir['#{File.dirname(__FILE__)}/lib/rake/*.rake'].map{|f|requiref}它加载文件没问题，但没有任务。我现在只有一个.rake文件作为测试，名为“servers.rake”，它看起来像这样:namespace:serverdotask:testdoputs"test"endend所以当我运行rakeserver:testid时

  4. ruby-on-rails - Ruby net/ldap 模块中的内存泄漏 - 2

    作为我的Rails应用程序的一部分，我编写了一个小导入程序，它从我们的LDAP系统中吸取数据并将其塞入一个用户表中。不幸的是，与LDAP相关的代码在遍历我们的32K用户时泄漏了大量内存，我一直无法弄清楚如何解决这个问题。这个问题似乎在某种程度上与LDAP库有关，因为当我删除对LDAP内容的调用时，内存使用情况会很好地稳定下来。此外，不断增加的对象是Net::BER::BerIdentifiedString和Net::BER::BerIdentifiedArray，它们都是LDAP库的一部分。当我运行导入时，内存使用量最终达到超过1GB的峰值。如果问题存在，我需要找到一些方法来更正我的代

  5. python - 如何使用 Ruby 或 Python 创建一系列高音调和低音调的蜂鸣声？ - 2

    关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。

  6. ruby-on-rails - Rails 3 中的多个路由文件 - 2

    Rails2.3可以选择随时使用RouteSet#add_configuration_file添加更多路由。是否可以在Rails3项目中做同样的事情？ 最佳答案 在config/application.rb中:config.paths.config.routes在Rails3.2(也可能是Rails3.1)中，使用:config.paths["config/routes"] 关于ruby-on-rails-Rails3中的多个路由文件，我们在StackOverflow上找到一个类似的问题

  7. ruby-on-rails - 如何验证 update_all 是否实际在 Rails 中更新 - 2

    给定这段代码defcreate@upgrades=User.update_all(["role=?","upgraded"],:id=>params[:upgrade])redirect_toadmin_upgrades_path,:notice=>"Successfullyupgradeduser."end我如何在该操作中实际验证它们是否已保存或未重定向到适当的页面和消息？ 最佳答案 在Rails3中，update_all不返回任何有意义的信息，除了已更新的记录数(这可能取决于您的DBMS是否返回该信息)。http://ar.ru

  8. ruby-on-rails - 'compass watch' 是如何工作的/它是如何与 rails 一起使用的 - 2

    我在我的项目目录中完成了compasscreate.和compassinitrails。几个问题:我已将我的.sass文件放在public/stylesheets中。这是放置它们的正确位置吗？当我运行compasswatch时，它不会自动编译这些.sass文件。我必须手动指定文件:compasswatchpublic/stylesheets/myfile.sass等。如何让它自动运行？文件ie.css、print.css和screen.css已放在stylesheets/compiled。如何在编译后不让它们重新出现的情况下删除它们？我自己编译的.sass文件编译成compiled/t

  9. ruby - 如何将脚本文件的末尾读取为数据文件(Perl 或任何其他语言) - 2

    我正在寻找执行以下操作的正确语法(在Perl、Shell或Ruby中):#variabletoaccessthedatalinesappendedasafileEND_OF_SCRIPT_MARKERrawdatastartshereanditcontinues. 最佳答案 Perl用__DATA__做这个:#!/usr/bin/perlusestrict;usewarnings;while(){print;}__DATA__Texttoprintgoeshere 关于ruby-如何将脚

  10. ruby - 如何以所有可能的方式将字符串拆分为长度最多为 3 的连续子字符串？ - 2

    我试图获取一个长度在1到10之间的字符串，并输出将字符串分解为大小为1、2或3的连续子字符串的所有可能方式。例如:输入:123456将整数分割成单个字符，然后继续查找组合。该代码将返回以下所有数组。[1,2,3,4,5,6][12,3,4,5,6][1,23,4,5,6][1,2,34,5,6][1,2,3,45,6][1,2,3,4,56][12,34,5,6][12,3,45,6][12,3,4,56][1,23,45,6][1,2,34,56][1,23,4,56][12,34,56][123,4,5,6][1,234,5,6][1,2,345,6][1,2,3,456][123

随机推荐