oracle 如何从字符串中删除最后一个数字

bxjv4tth  于 2023-11-17  发布在  Oracle
关注(0)|答案(4)|浏览(173)

我想删除字符串的最后一个数字一个接一个使用收集,但当我删除最后一个数字,它也从开始删除零

declare
type t_list is table of mk_suk.CALLED_NBR%type;
v_array t_list;
idx number;
ids number;

begin

select substr(CALLED_NBR,1,(LENGTH(CALLED_NBR))) bulk collect into v_array from mk_suk;

for i in v_array.first..v_array.last loop
dbms_output.put_line(v_array(i));
ids:=v_array(i);

loop
select substr(ids,1,(LENGTH(ids)-1))  into idx from mk_suk where CALLED_NBR=v_array(i);

ids:=idx;

dbms_output.put_line(idx);

exit when length(idx)\<2;

end loop;
end loop;

end;

/

字符串
查询结果

09589583145
958958314
95895831
9589583
958958
95895
9589
958
95
9

ijxebb2r

ijxebb2r1#

oracle不会“从开始处删除零”,而是将其转换为数字。数字0001与1相同,变量idsidx都是数字。

koen 19c>SELECT LENGTH(0001) as anumber, LENGTH('0001') as astring from dual;

   ANUMBER    ASTRING 
__________ __________ 
         1          4 

koen 19c>

字符串
所以在一个循环中有一个区别:

koen 19c>declare
  2    l_string VARCHAR2(100) := '0123';
  3    l_nr NUMBER := 0123;
  4  begin
  5    FOR i IN 1 .. LENGTH(l_string) LOOP
  6      dbms_output.put_line('string:' || l_string);
  7      l_string := SUBSTR(l_string,1,LENGTH(l_string)-1);
  8    END LOOP;
  9    FOR i IN 1 .. LENGTH(l_nr) LOOP
 10      dbms_output.put_line('l_nr:' || l_nr);
 11      l_nr := SUBSTR(l_nr,1,LENGTH(l_nr)-1);
 12    END LOOP;
 13  END;
 14* /
string:0123
string:012
string:01
string:0
l_nr:123
l_nr:12
l_nr:1

PL/SQL procedure successfully completed.

vc9ivgsu

vc9ivgsu2#

你的代码是错误的,因为-在声明部分-你将两个变量都声明为NUMBER。如果它们是 * 字符串 *(并且代码被修改-为什么再次从表中获取,如果你已经在集合中有了那个值?),它 * 工作 *:
样本数据:

SQL> desc mk_suk
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CALLED_NBR                                         VARCHAR2(20)

SQL> select * from mk_suk;

CALLED_NBR
--------------------
09589583145

SQL> set serveroutput on

字符串
您的代码,已修改:

SQL> DECLARE
  2     TYPE t_list IS TABLE OF mk_suk.CALLED_NBR%TYPE;
  3     v_array  t_list;
  4     idx      mk_suk.CALLED_NBR%TYPE;  --   NUMBER;
  5     ids      mk_suk.CALLED_NBR%TYPE;  --   NUMBER;
  6  BEGIN
  7     SELECT SUBSTR (CALLED_NBR, 1, (LENGTH (CALLED_NBR)))
  8       BULK COLLECT INTO v_array
  9       FROM mk_suk;
 10
 11     FOR i IN v_array.FIRST .. v_array.LAST
 12     LOOP
 13        DBMS_OUTPUT.put_line (v_array (i));
 14
 15        ids := v_array (i);
 16
 17        LOOP
 18           idx := SUBSTR (ids, 1, (LENGTH (ids) - 1));
 19           ids := idx;
 20           DBMS_OUTPUT.put_line (idx);
 21
 22           EXIT WHEN LENGTH (idx) < 2;
 23        END LOOP;
 24     END LOOP;
 25  END;
 26  /


测试结果:

09589583145
0958958314
095895831
09589583
0958958
095895
09589
0958
095
09
0

PL/SQL procedure successfully completed.

SQL>

vcudknz3

vcudknz33#

您正在为idsidx变量使用NUMBER数据类型。NUMBER是一种二进制数据类型,每2位使用1个字节,并且不存储前导零或尾随十进制零(由于0123123123.000都具有相同的数值),并且从不以任何特定的人类可读格式存储(因为它使用二进制格式)。
如果你想保留前导零,那么不要使用NUMBER,而使用VARCHAR2,或者继续使用列数据类型MK_SUK.CALLED_NBR%TYPE
然而,你不需要所有的临时变量,可以简化代码:

DECLARE
  TYPE t_list IS TABLE OF mk_suk.CALLED_NBR%TYPE;
  v_array t_list;
BEGIN
  SELECT called_nbr
  BULK COLLECT INTO v_array
  FROM   mk_suk;

  FOR i IN 1 .. v_array.COUNT LOOP
    FOR l IN REVERSE 1 .. LENGTH(v_array(i)) LOOP
      DBMS_OUTPUT.PUT_LINE(SUBSTR(v_array(i), 1, l));
    END LOOP;
  END LOOP;
END;
/

字符串
然后,如果你有样本数据:

CREATE TABLE mk_suk (called_nbr) AS
SELECT '09589583145' FROM DUAL;


输出为:

09589583145
0958958314
095895831
09589583
0958958
095895
09589
0958
095
09
0


fiddle

mw3dktmi

mw3dktmi4#

你有没有考虑过用一个简单的SQL来做这件事:

Select      SubStr(CALLED_NBR, 1, LEVEL) "NBRS"
From      ( Select '09589583145' "CALLED_NBR" From  Dual )
Connect By  LEVEL <= Length(CALLED_NBR)
Order BY    LEVEL Desc

--  NBRS       
--  -----------
--  09589583145
--  0958958314
--  095895831
--  09589583
--  0958958
--  095895
--  09589
--  0958
--  095
--  09
--  0

字符串
如果目标是使用collection来解决这个问题,那么相同的SQL可以在PL/SQL块中完成这项工作,如下所示:

SET SERVEROUTPUT ON
DECLARE
  TYPE t_list IS TABLE OF VARCHAR2(20);
  v_array t_list;
BEGIN
  Select      SubStr(CALLED_NBR, 1, LEVEL) "NBRS" BULK COLLECT INTO v_array
  From        ( Select '09589583145' "CALLED_NBR" From  Dual )
  Connect By  LEVEL <= Length(CALLED_NBR)
  Order BY    LEVEL Desc;
  --
  FOR i IN 1 .. v_array.Count LOOP
    DBMS_OUTPUT.PUT_LINE(v_array(i));
  END LOOP;
END;
/

--  09589583145
--  0958958314
--  095895831
--  09589583
--  0958958
--  095895
--  09589
--  0958
--  095
--  09
--  0
--  
--  PL/SQL procedure successfully completed.

相关问题