CODEWITHSUNDEEP
sqlplsqloracle10g
surya
surya

Reputation: 1359

input string to table

I am doing some debugging in SQL for oracle 10g. I have a big input string which is used in "IN Clause" i.e. 

select * from table where col in ('str2','str3','str4','str5',...)

i want to convert the in clause to rows or table? Is there a way to do this i.e. 

select 'str2','str3','str4','str5', .. from dual

but this outputs multiple columns and i want multiple rows?

Edit:

Here is what i am trying to do. suppose i have an excel data in tmp_table1 (cant create in reality) and tmp_table1 is same as the IN clause, then the below statement will give the missing keys.

SELECT *
  FROM tmp_table1
 WHERE unique_id NOT IN (
               SELECT unique_id
                 FROM table1
                WHERE unique_id IN
                                 ('str1', 'str2', 'str3', 'str4'))

now @andriy-m solution works if the in string is less than 4000. but what if its greater?

Upvotes: 1

Views: 759

Answers (2)

Andriy M
Andriy M

Reputation: 77657

You are probably looking for this solution.

Upvotes: 2

Dan J
Dan J

Reputation: 16708

You can UNION the values into multiple rows:

SELECT 'str2' AS col FROM dual
UNION
SELECT 'str3' FROM dual
UNION
SELECT 'str4' FROM dual
UNION
SELECT 'str5' FROM dual

Upvotes: 0

Related Questions