CODEWITHSUNDEEP
sqloracle-databasesplit
Rahul Hendawe
Rahul Hendawe

Reputation: 912

Escape single quotes from comma separated string in Oracle

I am trying to escape single quotes from the comma separated string in Oracle SQL Developer, below is my Select query:

SELECT (CASE WHEN PS.SUPPLIER_NO IS NULL THEN 'FALSE' ELSE 'TRUE' END) AS "Check"
      ,dm."branch_id", dm."SUPPLIER_NO", dm."supplier_name", dm."date_created"
      ,dm."vendor_no", dm."ORDERING_ADDRESS_1", dm."ORDERING_ADDRESS_2"
      ,dm."ORDERING_CITY", dm."ORDERING_STATE_COD", dm."ORDERING_ZIP"
      ,dm."country_code", dm."fax_area_code", dm."fax_no", dm."fax_extension"
FROM [email protected] dm
LEFT JOIN PROJECT_SUPPLIERS PS ON PS.SUPPLIER_NO = dm."SUPPLIER_NO"
 AND PS.BRANCH_ID = dm."branch_id"
 AND PS.PROJECT_ID = 'e82a654af6c64e8297576b88b5eff138'
WHERE dm."branch_id" IN (REPLACE('6218, 5577','''',''''''));

I tried to replace/escape the single quotes from Where IN clause but it gives error of invalid number.

ORA-01722: invalid number

When I am trying to select same string using replace from other select statement it is working.

select REPLACE('6218, 5577','''','''''') from dual;

above query works as expected and gives o/p as '6218, 5577'.

Can anyone please advise, Why it is not working in my main Select statement?

Upvotes: 0

Views: 1559

Answers (1)

hotfix
hotfix

Reputation: 3396

'6218, 5577' this is a string and not a list of values. So if you do select REPLACE('6218, 5577','''','''''') from dual; you are trying to replace single Quote in your string. since it does not exists in your string, nothing will be replaced.

the result of you select is still the same string and not a list as you expect.

you should split a comma delimited string in rows.

here is one way to do that 

with tab as (
  SELECT trim(regexp_substr('6218, 5577', '[^,]+', 1, LEVEL)) str
   FROM dual
   CONNECT BY instr('6218, 5577', ',', 1, LEVEL - 1) > 0
)

than you can use it on your select

with tab as (
  SELECT trim(regexp_substr('6218, 5577', '[^,]+', 1, LEVEL)) str
   FROM dual
   CONNECT BY instr('6218, 5577', ',', 1, LEVEL - 1) > 0
) 
select ...
  from ...
  WHERE dm."branch_id" IN (select str from tab );

Upvotes: 2

Related Questions