sql - 如何从oracle sql中的内部查询输出中删除单引号

Question

我想从内部查询的输出中删除单引号：

select *from demo where codes in (select codes from demo_temp);

位列的数据类型是 varchar2

的输出select codes from demo_temp是'201,601'

我希望输出为

select *from demo where codes in (201,601);---15rows

我在下面尝试过，但它不起作用：

select *from demo where codes in replace((select codes from demo_temp),'''','');--0rows

select *from demo where codes in (select replace(codes,'''','') from demo_temp);--0rows

标签： sqldatabaseoracle

Answer 1

一种选择是拆分codes为行（在子查询中）：

SELECT *
  FROM demo
 WHERE codes IN (    SELECT REGEXP_SUBSTR (TRIM (BOTH '''' FROM codes),
                                           '[^,]+',
                                           1,
                                           LEVEL)
                       FROM demo_temp
                 CONNECT BY LEVEL <= REGEXP_COUNT (codes, ',') + 1);

---

它会起作用吗？我猜是这样，如果demo.codes包含诸如201and之类的值601。

例如：demo_temp.codes = '201,601'。拆分为行、查询返回201和601（没有单引号）：

SQL> WITH demo_temp AS (SELECT q'['201,601']' codes FROM DUAL)
  2      SELECT codes,
  3             REGEXP_SUBSTR (TRIM (BOTH '''' FROM codes),
  4                            '[^,]+',
  5                            1,
  6                            LEVEL)
  7        FROM demo_temp
  8  CONNECT BY LEVEL <= REGEXP_COUNT (codes, ',') + 1
  9  /

CODES     REGEXP_SU
--------- ---------
'201,601' 201
'201,601' 601

SQL>