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

Question

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

Does your inner table have one row with '201,601'? Or multiple rows with comma-separated lists? Or just one row per number like '201' and '601'? Comma-separated list strings don't belong in a database. — Thorsten Kettner
– Thorsten Kettner, Commented Sep 23, 2021 at 6:15
I think it does not explain very well whether he was to convert the comma separated values to rows, or just remove the single quotes, or both — Roberto Hernandez
– Roberto Hernandez, Commented Sep 23, 2021 at 6:16
i need just to remove single quotes from the inner query output — Mudit B
– Mudit B, Commented Sep 23, 2021 at 6:33

Littlefoot · Accepted Answer · 2021-09-23 06:04:14Z

2

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>

answered Sep 23, 2021 at 6:04

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mudit B Over a year ago

Is there any way we can get the output as 201,601 in the same row?

Littlefoot Over a year ago

What does that mean? Which "same row"? If you're talking about what you'd want to get (and posted code ... where codes in (201,601)) then forget about it. It just won't work that way. Correct way is to do what I suggested.

Mudit B Over a year ago

i have also tried this: select regexp_substr(codes,'\w+',1,1) codesfrom demo_temp; it is returning 201 only can u suggest on this as well

Littlefoot Over a year ago

Certainly. It looks as if you don't know REGEXP_SUBSTR syntax so you're "lucky guessing". I posted code that works, so I suggest you use it.

Roberto Hernandez · Accepted Answer · 2021-09-23 16:48:11Z

0

As the poster of the question requires dynamic comparison, let's do it with a simple test. The idea is to show the original column without quotes and the values in the join that matches any of the values in the comma string

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

Column original in the same row without quotes

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 , replace(x.codes,'''','') as org_val 
from t cross join x 
where t.codes in ( select z.codes from z )
and
( t.codes like '%' || regexp_substr(replace(x.codes,'''',''),'[^,]+',1,1) || '%' 
  or 
  t.codes like '%' || regexp_substr(replace(x.codes,'''',''),'[^,]+',1,2) || '%'
);


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

edited Sep 23, 2021 at 16:48

answered Sep 23, 2021 at 6:12

Roberto Hernandez

8,5933 gold badges22 silver badges51 bronze badges

6 Comments

Mudit B Over a year ago

I will consider first solution but you have hardcoded the values in where clause as where replace(codes,'''','') = '201,601' , I need some dynamic generic solution

Roberto Hernandez Over a year ago

@MuditB , you did not say anything about dynamic in your original question. let me show you that in the same answer.

Roberto Hernandez Over a year ago

you have on my answer a solution that works for any data you might have in both tables

Mudit B Over a year ago

Is there any way we can get the output as 201,601 in the same row?

Mudit B Over a year ago

i have also tried this: select regexp_substr(codes,'\w+',1,1) codesfrom demo_temp; it is returning 201 only can u suggest on this as well

|

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related