ORA-01722数字到varchar比较错误

Question

我有一个包含以下数据的表，本想使用"IN“子句将此表数据与另一个表进行比较，但抛出ORA-01722错误，

SQL> desc a
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCOPE                                              VARCHAR2(20)
 PART_LIST                                          VARCHAR2(2000)

SQL> select part_list from a ;

PART_LIST
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16


SQL> select partition_name from dba_Tab_partitions where partition_position in (select trim((part_list) from a);

                                                                           
ERROR at line 1:
ORA-01722: invalid number

向Kannan致敬

Answer 1

您需要首先将逗号分隔的字符串转换为行，然后才能像这样使用它们。请尝试下面的查询。

   select partition_name from dba_Tab_partitions where partition_position in (
   select  to_number(trim(regexp_substr(PART_LIST,'[^,]+', 1, level) )) value
   from a
   connect by regexp_substr(PART_LIST, '[^,]+', 1, level) is not null
   )