CODEWITHSUNDEEP
oracle-databaseoracle11g
TheNewby
TheNewby

Reputation: 107

SQL find number inside a Comma seperated string

I'm trying to find a number (ID) inside Comma seperated string. I tried it with a Select but didn't work. Is there another Method I could use?

SELECT CASE WHEN TO_CHAR(1) IN ('1, 2, 3, 4') THEN 1 ELSE 0 END AS Result
FROM DUAL

I always get '0' . Can anyone please help here.

Upvotes: 2

Views: 947

Answers (4)

Goran Stefanović
Goran Stefanović

Reputation: 328

One of the oldest techniques to achieve this is using LIKE:

with t as
(
  select replace('1, 2, 3, 4', ' ', '') text from dual
)
select case
         when ',' || text || ',' like '%,1,%' then 1
         else 0
       end result
  from t;

I removed unnecessary spaces from the string, and than did comparison by adding commas to the beginning and end of both strings (it has to be done in order to eliminate numbers that contain 1, but are not 1: 10, 11, 21...).

Or to make it more clear:

select 1
  from dual
 where ',1,2,3,4,' like '%,1,%';

Upvotes: 1

sprimorac
sprimorac

Reputation: 36

Your query does not return 1 because your query is testing this is '1' = '1, 2, 3, 4' (and that returns false)

This query returns 1

SELECT CASE WHEN TO_CHAR(1) IN ('1', '2', '3', '4') THEN 1 ELSE 0 END AS Result
FROM DUAL


SELECT CASE WHEN instr('1, 2, 3, 4', '1') = 1 THEN 1 ELSE 0 END AS Result
FROM DUAL

SELECT CASE WHEN instr('11, 2, 3, 4', '1') = 1 THEN 1 ELSE 0 END AS Result
FROM DUAL

Maybe the function str2tbl (returns numbers from string) will give you what you need:

CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;

CREATE OR REPLACE function str2tbl( p_str in varchar2 ) return NUMBER_NTT
as
       l_str   long default p_str || ',';
        l_n        number;
        l_data    NUMBER_NTT := NUMBER_NTT();
    begin
        loop
            l_n := instr( l_str, ',' );
            exit when (nvl(l_n,0) = 0);
           l_data.extend;
           l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
           l_str := substr( l_str, l_n+1 );
       end loop;
       return l_data;
   end;
/

select column_value num_val from THE (select cast( str2tbl('1, 2, 3, 4') as number_ntt) num_val from dual) --where column_value =1

Upvotes: 2

Kacper
Kacper

Reputation: 4818

Such query can convert for you comma separated list into table:

select regexp_substr('1,2,3,4','[^,]+', 1, level) val from dual
  connect by regexp_substr('1,2,3,4', '[^,]+', 1, level) is not null;

Then you can perform in on result of the query.

Another nice option is: 

select to_number(column_value) as IDs from xmltable('1,2,3,4,5');

Upvotes: 4

William Robertson
William Robertson

Reputation: 15991

A 'comma-separated string' is not a thing in SQL or PL/SQL, so the IN() construction is not going to parse it into individual values for you. What you need is a text pattern matching expression, e.g. something like

select case
           when regexp_like('1, 2, 3, 4', '(^| )1($|,)') then 'Yes' else 'No'
       end as matched
from   dual;

Upvotes: 3

Related Questions