How to turn string with commas into enum in SQL / PLSQL

Question

I've got interesting question.

How can I turn '1,4' into enum 1,4 to be able to pass it in expression like this:

SELECT DICT_ID AS ITEM_ID FROM DICTIONARY WHERE SERIAL_NUMBER IN (#ARGUMENT#)

Where #ARGUMENT# is '1,4'.

In short, I want to make this query work like if we've passed not a string, but numbers.

Why am I passing '1,4' insteard of just passing numbers - well not me passing this string in query, but some application.

I can edit query, but not #ARGUMENT#

What your need is to convert a string to an array of integer... Check http://stackoverflow.com/questions/3819375/convert-comma-separated-string-to-array-in-pl-sql — araknoid, Feb 28 '13 at 08:05

score 3 · Accepted Answer · answered Feb 28 '13 at 08:18

Something like:

select dict_id as item_id
from dictionary d
where serial_number in  (
     select to_number(regexp_substr ('1,2,3,4','[^,]+',1,level)) as id
     from dual
     connect by regexp_substr ('1,2,3,4','[^,]+',1,level) is not null
)

Note: I found this on SO some time ago. But I can't find the link any more to give credits to the original author. If anyone knows, I'll gladly replace this with a link to the original

Well it's not so bad if your answer is dublicating another, it will be simplier to find an answer however — Alexander.Iljushkin, Feb 28 '13 at 09:56

score 1 · Answer 2 · answered Feb 28 '13 at 08:14

1

SELECT 
      DICT_ID AS ITEM_ID 
   FROM 
      DICTIONARY 
   WHERE 
      INSTR(','||#ARGUMENT#||',', ','||SERIAL_NUMBER||',') > 0

answered Feb 28 '13 at 08:14

Egor Skriptunoff

23,359
2
34
64

How to turn string with commas into enum in SQL / PLSQL

2 Answers2