CODEWITHSUNDEEP
sqloracle-databasereplace
thamarai
thamarai

Reputation: 31

ORACLE SQL IN Clause (SQL Query)

I'm having : delimited column like 1:2:3:. I want to get this into 1,2,3. My query looks like,

select name 
from status where id IN (SELECT REPLACE(NEXT_LIST,':',',')
                         FROM status);

but I got an error

ORA-01722: invalid number

Upvotes: 1

Views: 115

Answers (3)

diziaq
diziaq

Reputation: 7795

The REPLACE function returns a string, so the nested query returns a list of string values (where colons replaced with commas), but not a list of number values. When Oracle engine interprets id IN (str_value) it tries to cast the str_value to number and raises exception ORA-01722: invalid number because there are cases like '1:2:3' which are definetely unparseable.

The "pure sql" approach leads us to using custom function detecting if a number is in a colon-separated list:

-- you need Oracle 12c to use function in the WITH clause
-- on earlier versions just unwrap CASE statement and put it into query
WITH
  FUNCTION in_list(p_id NUMBER, p_list VARCHAR2) RETURN NUMBER DETERMINISTIC IS
  BEGIN
    RETURN CASE WHEN
           instr(':' || p_list || ':', ':' || p_id || ':') > 0        
      THEN 1 ELSE 0 END;
  END;
SELECT *
FROM status 
WHERE in_list(id, next_list) = 1;

Here I assume that values in the next_list column are strings containing numbers separated with colon without spaces. In common case you shall modify the function to match specific list formats.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

(1, 2, 3, 4) is different from ('1, 2, 3, 4'). IN requires the former, a list of values; you give it the latter, a string.

You have two options mainly:

  1. Build the query dynamically, i.e. get the list first, then use this to build a query string.
  2. Tokenize the string. This can be done with a custom pipelined function or a recursive query, maybe also via some XML functions. Google "Oracle tokenize string" to find a method that suits you.

UPDATE Option #3: Use LIKE as in ':1:2:3:4:' like '%:3:%' (This requires your next_list to contain only simple numbers separated with colons. No leading zeros, no blanks, no other characters.)

select name 
from status 
where (select ':' || next_list || ':' from status) like '%:' || id || ':%'

Upvotes: 2

Raffaello.D.Huke
Raffaello.D.Huke

Reputation: 552

i agreed with Thorsten but i wonder if we just replace one more time would it works? i mean like this:

 select name 
 from status where id IN (SELECT replace(REPLACE(NEXT_LIST,':',','),'''','')
                            FROM status);

Upvotes: 1

Related Questions