works in Oracle, hope there is something similar to this in Sql Server
too! There might be some other good way to do this, if so, please let
SELECT * FROM TABLE1 WHERE COLUMN1 IN (‘ABC’,’XYZ’,’PQR’)
suppose the strings ‘ABC’,’XYZ’ and ’PQR’ need to be grouped as a
single string in the frontend and to be passed as a parameter. How can
we do it? We cannot write like this –
SELECT * FROM TABLE1 WHERE COLUMN1 IN (IN_PARAMTER)
There are many ways to do it:
There is one more way to do it and found it to be easy, so thought of sharing.
Here it is:
SELECT * FROM TABLE1 WHERE COLUMN1 IN ( SELECT REGEXP_SUBSTR(In_Parameter ,'[^,]+',1,LEVEL) FROM DUAL CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (In_Parameter, '[^,]')) + 1 )
It is enough if we pass the parameter as ‘ABC,XYZ,PQR’ instead of “‘ABC’,’XYZ’,’PQR’”.
More about Oracle Regular Expressions - http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10759/functions116.htm
The opinions expressed herein are my own personal opinions and do not represent
my employer's view in any way.