0

Suppose my input is "1, 2, 3" and I want to use IN(1, 2, 3) but I am not able to. In MYSQL I could have used FIND_IN_SET but here I am not able to find anything.

MT0
  • 143,790
  • 11
  • 59
  • 117
Aditya Dhiman
  • 19
  • 6
  • 1
    Does this answer your question? [Is it possible to query a comma separated column for a specific value?](https://stackoverflow.com/questions/7212282/is-it-possible-to-query-a-comma-separated-column-for-a-specific-value) or [Oracle SQL comma separated parameter in where clause](https://stackoverflow.com/questions/57849397/oracle-sql-comma-separated-parameter-in-where-clause) – astentx Jul 13 '22 at 11:20

2 Answers2

1

You do not need to split the string and can use LIKE (making sure you also match the surrounding delimiters):

SELECT *
FROM   table_name
WHERE  ', ' || :your_list || ', ' LIKE '%, ' || column_to_match || ', %'

Which for your hard-coded list would be:

SELECT *
FROM   table_name
WHERE  ', 1, 2, 3, ' LIKE '%, ' || column_to_match || ', %'

Which, for the sample data:

CREATE TABLE table_name (name, column_to_match) AS
SELECT 'A', 1 FROM DUAL UNION ALL
SELECT 'B', 2 FROM DUAL UNION ALL
SELECT 'C', 3 FROM DUAL UNION ALL
SELECT 'D', 4 FROM DUAL;

Outputs:

NAME COLUMN_TO_MATCH
A 1
B 2
C 3

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • @MTO, I am getting these values ‘1,2,3,4’ as an input to my procedure and I need to pick the data from a certain table matching these values but I can’t use these directly like IN (‘1,2,3,4’). Therefore I need a way to covert them to number type to use it as IN (1,2,3,4) – Aditya Dhiman Jul 13 '22 at 11:21
  • @AdityaDhiman You cannot do that as you are passing in a single string value and not a list of numbers and it is impossible to convert from one to the other. As I said in the answer, use `LIKE` to perform a sub-string match (including the delimiters in the match). – MT0 Jul 13 '22 at 11:23
0

One option is to split that string into rows.

On Scott's sample schema:

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Here's how:

SQL> select * from dept
  2  where deptno in (select regexp_substr('&&par_deptno', '[^,]+', 1, level)
  3                   from dual
  4                   connect by level <= regexp_count('&&par_deptno', ',') + 1
  5                  );
Enter value for par_deptno: 10,30,40

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        40 OPERATIONS     BOSTON
        30 SALES          CHICAGO

SQL>

Depending on tool you use, you might have to change substitution variable with a bind variable, i.e. use

... select regexp_substr(:par_deptno, ...

instead.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • I am getting these values ‘1,2,3,4’ as an input to my procedure and I need to pick the data from a certain table matching these values but I can’t use these directly like IN (‘1,2,3,4’). Therefore I need a way to covert them to number type to use it as IN (1,2,3,4) – Aditya Dhiman Jul 13 '22 at 11:22
  • If you have values enclosed into single quotes, **remove them** first; the rest of query remains *as is*: `select regexp_substr(replace(:par_deptno, chr(39), null), '[^,]+', 1, level) ...` – Littlefoot Jul 13 '22 at 11:24