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:
- We can write an inline query.
- We can split the strings and group it as a table type.
- We can write dynamic sql.
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)
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