how to remove single quotes from the output of inner query in oracle sql-CodePudding

I want to remove single quotes from the output of inner query as:

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

Datatypes of bith columns are varchar2

output of select codes from demo_temp is '201,601'

I want the output to be as

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

I have tried below but its not working:

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

CodePudding user response：

One option is to split codes into rows (in a subquery):

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);

Will it work? I guess so, if demo.codes contains values such as 201 and 601.

For example: demo_temp.codes = '201,601'. Split into rows, query returns 201 and 601 (with no single quotes):

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>

CodePudding user response：

Your question of removing single quotes can be done easy with replace

SQL> with x as ( select q'('201,601')' as codes from dual )
     select * from x ;

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

SQL> with x as ( select q'('201,601')' as codes from dual )
  2  select * from x where replace(codes,'''','') = '201,601' ;

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

SQL>  with x as ( select q'('201,601')' as codes from dual )
  2  select replace(codes,'''','')  as codes from x
  3  where replace(codes,'''','') = '201,601' ;

CODES
-------
201,601

Now, you have an original table with codes in the form of rows that you want to compare to the single values of this list

One option would be - I split the operation in steps to make clear how, but it can be consolidated in less steps

SQL> with t ( codes ) as 
  2  ( select 201 from dual union all
  3    select 601 from dual union all
  4    select 309 from dual
  5  ), -- original codes in table t
  6  x ( codes )
  7  as
  8  ( select replace(codes,'''','') from ( select q'('201,601')' as codes from dual )
  9    where replace(codes,'''','') = '201,601'
 10  ), -- codes without single quote 
 11  z ( lvl , codes )
 12  as
 13  ( select level lvl, regexp_substr ( codes, '[^,] ', 1, LEVEL) as codes from x
 14    connect by level <= regexp_count (codes, ',')   1
 15  ) -- codes split into rows
 16  select z.codes , t.codes from z join t on z.codes = t.codes
 17*
SQL> /

CODES                             CODES
---------------------------- ----------
201                                 201
601                                 601

UPDATE

As the poster of the question requires dynamic comparison, let's do it with a simple test

SQL> create table t ( id number generated always as identity start with 1 increment by 1 , codes number ) ;

Table created.

SQL> insert into t ( codes ) values ( 201 ) ;

1 row created.

SQL> insert into t ( codes ) values ( 601 ) ;

1 row created.

SQL> insert into t ( codes ) values ( 309 ) ;

1 row created.

SQL> insert into t ( codes ) values ( 501 )  ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from t ;

        ID      CODES
---------- ----------
         1        201
         2        601
         3        309
         4        501

SQL> create table x ( codes varchar2(20) ) ;

Table created.

SQL> insert into x values ( q'('201,601')' ) ;

1 row created.

SQL> insert into x values ( q'('309,501')') ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from x ;

CODES
--------------------
'201,601'
'309,501'

SQL> with z ( lvl , codes )
    as
    ( select level lvl, regexp_substr ( codes, '[^,] ', 1, LEVEL) as codes
  from ( select replace(codes,'''','') as codes from x )
  connect by level <= regexp_count (codes, ',')   1
  )
  select t.id , t.codes from t
  where t.codes in ( select z.codes from z );

        ID      CODES
---------- ----------
         1        201
         2        601
         4        501
         3        309