Nodos Nodos - 1 year ago 84
PHP Question

Oracle use a string inside in function

I have a select statement where I use IN function, and inside the IN function 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 Source

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.