0

I have a select query which is working in postgres , but not in Oracle

The Select Query Uses regexp_split_to_array , which is not suppourted in Oracle

The regexp_split_to_array used here is to filter non working days

select 
  * 
from 
  department 
where 
  dept_status = 'A' 
  AND NOT (
    #{DAY} = any ( regexp_split_to_array(lower(non_working_days), ',')))
  AND dept_loc = 'US'

http://sqlfiddle.com/#!4/fbac4/4

  • Does this answer your question? [PL/SQL - comma separated list within IN CLAUSE](https://stackoverflow.com/questions/12800180/pl-sql-comma-separated-list-within-in-clause) – astentx Jan 07 '23 at 21:45

1 Answers1

0

Oracle does not have a function regexp_split_to_array . Instead you can use LIKE (which is much faster than regular expressions) to check for a sub-string match (including the surrounding delimiters so you match a complete term):

SELECT * 
FROM   department 
WHERE  dept_status = 'A' 
AND    ',' || lower(non_working_days) || ',' NOT LIKE '%,' || :DAY || ',%'
AND    dept_loc = 'US'

Note: You should pass in variables using bind variables such as :day or ? (for an anonymous bind variable) rather than relying on a pre-processor to replace strings as it will help to prevent SQL injection attacks.

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117
  • I should pass the bind variablr as day –  Jan 07 '23 at 21:19
  • Thank u so much, can't we do the same using reg sub str of oracle? –  Jan 07 '23 at 21:19
  • @gooddog I have not idea what you are using (i.e. Java, C#, PHP, etc.) to pass the bind variable; without knowing that it is impossible to contemplate how you would pass it. – MT0 Jan 07 '23 at 21:22
  • Yes, you could write an expression using regular expressions to split the string and then match individual terms; but why would you want to when looking for matching sub-strings is much more efficient? (And would probably also be more efficient in PostgreSQL) – MT0 Jan 07 '23 at 21:23
  • I am using java to pass the bind variable, which is working in postgres –  Jan 07 '23 at 21:25
  • @gooddog Java supports named and anonymous bind variables with Oracle as well as with PostgreSQL; use whichever type of bind variable you prefer. – MT0 Jan 07 '23 at 21:26