当数字有多个差异时，SQL 为字段指定一个名称

Question

我有一张这样的 table 。

ID   Name        Year
404  Matt        2015
406  Matt 1      2015
304  Matt 2      2015
334  Matt3       2015
655  Matt4       2015
923  Matt 5      2015
856  Banana      2015
274  Banana 2    2015
716  Banana7     2015
472  Orangutan   2015
844  OrangutanA 2015
159  Orangutan 2 2015
739  Big Foot    2015
866  Big Foot B  2015
273  Big Foot 5  2015
896  Big FootB   2015

我想在此表中添加一个额外的列，用于对名称进行分组(忽略数字和后面的字母)

如果只是名字后面的数字，我会删除数字，但有些有字母 a、b、c 等，有些有字母和数字!

我的预期输出是。

ID   Name        Year  SName
404  Matt        2015  Matt
406  Matt 1      2015  Matt
304  Matt 2      2015  Matt
334  Matt3       2015  Matt
655  Matt4       2015  Matt
923  Matt 5      2015  Matt
856  Banana      2015  Banana
274  Banana 2    2015  Banana
716  Banana7     2015  Banana
472  Orangutan   2015  Orangutan
844  OrangutanA  2015  Orangutan
159  Orangutan 2 2015  Orangutan
739  Big Foot    2015  Big Foot
866  Big Foot B  2015  Big Foot
273  Big Foot 5  2015  Big Foot
896  Big FootB   2015  Big Foot

SQL fiddle :http://sqlfiddle.com/#!4/3e7c7

输入脚本:

CREATE TABLE mytable
    ("ID" int, "Name" varchar2(11), "Year" int)
;

INSERT ALL 
    INTO mytable ("ID", "Name", "Year")
         VALUES (404, 'Matt', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (406, 'Matt 1', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (304, 'Matt 2', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (334, 'Matt3', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (655, 'Matt4', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (923, 'Matt 5', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (856, 'Banana', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (274, 'Banana 2', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (716, 'Banana7', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (472, 'Orangutan', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (844, 'OrangutanA', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (159, 'Orangutan 2', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (739, 'Big Foot', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (866, 'Big Foot B', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (273, 'Big Foot 5', 2015)
    INTO mytable ("ID", "Name", "Year")
         VALUES (896, 'Big FootB', 2015)
SELECT * FROM dual
;

Answer 1

如果您尝试应用的规则只是您想要删除值末尾的单个数字或大写字符，前面有或没有单个空格；并且您的真实数据不像评论中推测的那样模棱两可，而且复杂情况和边缘情况确实不适用；那么您可以使用相当简单的正则表达式来删除模式:

select "ID", "Name", "Year",
  regexp_replace("Name", '[ ]?[[:upper:][:digit:]]$', null) as "SName"
from mytable;

        ID Name              Year SName                                            
---------- ----------- ---------- --------------------------------------------------
       404 Matt              2015 Matt                                              
       406 Matt 1            2015 Matt                                              
       304 Matt 2            2015 Matt                                              
       334 Matt3             2015 Matt                                              
       655 Matt4             2015 Matt                                              
       923 Matt 5            2015 Matt                                              
       856 Banana            2015 Banana                                            
       274 Banana 2          2015 Banana                                            
       716 Banana7           2015 Banana                                            
       472 Orangutan         2015 Orangutan                                         
       844 OrangutanA        2015 Orangutan                                         
       159 Orangutan 2       2015 Orangutan                                         
       739 Big Foot          2015 Big Foot                                          
       866 Big Foot B        2015 Big Foot                                          
       273 Big Foot 5        2015 Big Foot                                          
       896 Big FootB         2015 Big Foot                                          

SQL Fiddle .

或者按照@LalitKumarB 的建议，使用虚拟列:

alter table mytable add ("SName" varchar2(11) as
  (cast(regexp_replace("Name", '[ ]?[[:upper:][:digit:]]$', null) as varchar2(11))));

SQL Fiddle .

cast() 是必需的，因为 regexp_replace() 返回的字符串不受大小限制 - 它不一定知道大小限制输入值的一部分，你可以让它更长——最多可以有 4000 个字符(或 12c 中的 32k)；所以 alter 没有它会得到 ORA-12899。

这些正在为您提供的示例数据获得所需的结果，但是如果您的规则确实更复杂并且您有更复杂的数据，那么它就需要修改 - 或者可能需要一种完全不同的方法(递归，示范条款，...取决于完整的要求)。