user75ponic user75ponic - 21 days ago 6
SQL Question

Pass String to SQL Statement In Condition

I have a string which is having values in the following format

A , B, C


I need to pass this to a SQL statement's in condition as

SELECT * FROM mytable
WHERE colname in ('A','B','C')


How can I do this?
I am not using PreparedStatement, plain SQL is being used.

Answer

In oracle you can use regexp_substr to achieve this. See below:

For example the input string is 'A,B,C'.

This can be achieved by splitting the comma separated string to individual strings and pass it to the IN clause.

First, we will form a query, that splits this comma separated string and gives the individual strings as rows.

Select regexp_substr('A,B,C' ,'[^,]+', 1, level) from dual
                connect by regexp_substr('A,B,C' ,'[^,]+', 1, level is not null

The above query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.

We can pass this query to our select statement to get the desired output.

SELECT * FROM mytable 
WHERE colname in (
                Select regexp_substr('A,B,C' ,'[^,]+', 1, level) from dual
                connect by regexp_substr('A,B,C' ,'[^,]+', 1, level) is not null )
Comments