Nodos Nodos - 3 months ago 7
SQL Question

Oracle use a string inside IN condition

I have a select statement where I use the IN condition, and inside the IN condition I have a string...how can I ignore the single quotes?

Select * from employ where id = 12 and org_id in ({$id})
$id = '12,13'


Thanks

Answer

in is used with collections, so your input string first should be converted into collection (by breaking into rows, based on comma delimiter)

Try something like this

Select * from employ where id = 12 and org_id in (
SELECT decode(:input_id,null,  (select  employ.org_id from dual) 
,TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)) )  
    FROM (SELECT  :input_id temp FROM DUAL)
    CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')
    )

by the way, this org_id in () will return true if :input_id is null.

Another approach would be to construct the whole query as a string first and then execute it either with execute immediate or through php. However this could raise sql injection concerns.