英文:
convert list of number to list of VARCHAR2 in ORACLE
问题
Sure, here's the translated code part:
需要将 (123456789012, 091234567812, 981234567890) 转换为 ('123456789012', '091234567812', '981234567890') 以在 SELECT * from table where field in ('123456789012', '091234567812', '981234567890') 中使用。原生SQL或PL/SQL解决方案均可。你可以帮助我吗？
未尝试任何操作。
英文:
Good day! I need convert (123456789012, 091234567812, 981234567890) to ('123456789012', '091234567812', '981234567890') for use it in SELECT * from table where field in ('123456789012', '091234567812', '981234567890'). Solution via native SQL or PL/SQL will suit any. Could you help me?
I not tried anything
答案1
得分: 2
I don't think it'll work as you expected. You should actually split that string (a column) into rows; something like this:
with temp (col) as
(select '123456789012, 091234567812, 981234567890' from dual)
select *
from your_table
where field in (select trim(regexp_substr(col, '[^,]+', 1, level))
from temp
connect by level <= regexp_count(col, ',') + 1
)
英文:
I don't think it'll work as you expected. You should actually split that string (a column) into rows; something like this:
with temp (col) as
(select '123456789012, 091234567812, 981234567890' from dual)
select *
from your_table
where field in (select trim(regexp_substr(col, '[^,]+', 1, level))
from temp
connect by level <= regexp_count(col, ',') + 1
)
答案2
得分: 0
只使用数字（不加引号）并查找子字符串匹配（使用周围的定界符字符以匹配整个术语）：
SELECT *
FROM table_name
WHERE ', ' || '123456789012, 091234567812, 981234567890' || ', '
LIKE '%, ' || field || ', %'
英文:
Just use the numbers (without quoting them) and look for sub-string matches (with the surrounding delimiter characters so that you match entire terms):
SELECT *
FROM table_name
WHERE ', ' || '123456789012, 091234567812, 981234567890' || ', '
LIKE '%, ' || field || ', %';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论