PL/SQL中怎样用sql语句或function函数处理以下数据

Question

SQL> select * from t;NUMBER_CODE          AREA_CODE
-------------------- ----------
9314977366           0931
2868005400           028
5303940098           0530
1051710900           010
1068317189           010已用时间:  00: 00: 00.20SQL> update t set number_code=substr(number_code,length(trim('0' from area_code||' ')))
  2  where number_code like trim(trim('0' from area_code||' '))||'%';已更新5行。已用时间:  00: 00: 00.10
SQL> select * from t;NUMBER_CODE          AREA_CODE
-------------------- ----------
4977366              0931
68005400             028
3940098              0530
51710900             010
68317189             010已用时间:  00: 00: 00.30
SQL>

Answer 1

create table t_test (
    number_code      varchar2(30),
    area_code          varchar2(10),
    primary key (number_code)
);insert into t_test values('1234567890','010');
insert into t_test values('101234567890','010');    
insert into t_test values('75584135221','0755');    
            
commit;select decode(substr(number_code, 1, length(area_code) - 1) -
              substr(area_code, 2),
              0,
              substr(number_code, length(area_code)),
              number_code)
  from t_test;    

Answer 2

to bzszp(SongZip):你的方法有个问题，如果是没有区号的号码，但起始几位与区号相同，那么也会处理掉了。
比如：
NUMBER_CODE          AREA_CODE
-------------------- ----------
21000000              021
28111111              028
...   

Answer 3

to bzszp(SongZip):你的方法有个问题，如果是没有区号的号码，但起始几位与区号相同，那么也会处理掉了。
比如：
NUMBER_CODE          AREA_CODE
-------------------- ----------
21000000              021
28111111              028
...   
这个问题根据你的需求是无法解决的就像这个号码2121212121         021
你说它是带区号的还是不带区号的？至少从你现有的需求中是分不出来的。所以你不能怪别人写的代码有问题

Answer 4

从数据来看,不带区号的号码至少为7位,所以可以通过替换后的号码是否大于等于7位来解决.insert into t_test values('2800000','028');
commit;select decode(sign(length(decode(substr(number_code,
                                        1,
                                        length(area_code) - 1) -
                                 substr(area_code, 2),
                                 0,
                                 substr(number_code, length(area_code)),
                                 number_code)) - 7),
              -1,
              number_code,
              decode(substr(number_code, 1, length(area_code) - 1) -
                     substr(area_code, 2),
                     0,
                     substr(number_code, length(area_code)),
                     number_code))
  from t_test;---------------------------------------------------------------
1 1234567890
2 1234567890
3 84135221
4 2800000