开发者

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?


You are probably looking for this solution.


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜