Oracle - Question about removing single quotes in Where-in clause-CodePudding

I have no idea about it. We insert the queries we want into our in-house enterprise database so that user can download the reports they want with parameters.

Query Example

SELECT *
FROM TABLE_A
WHERE name = ?

USER put parameter 'abc' when they download report, Then system will replace question mark to parameter user put like this.

SELECT * FROM TABLE_A WHERE name = 'abc'

But If I want to make where clause like below and user input id list 123,456,789, system will make like below.

WHERE ID in ('123,456,789')

In this case, How can I make like below? ID should not be string, So I need to remove single quotes.

WHERE ID in (123,456,789)

Please give me any idea. (I only can change query, not backend, frontend server)

CodePudding user response：

You do not need to split the string, use LIKE and include the delimiters (so you match an entire term rather than a partial match):

WHERE ',' || ? || ',' LIKE '%,' || id || ',%'

CodePudding user response：

resolved with below

REGEXP_SUBSTR('?', '[^,] ', 1, level) AS parts
FROM dual
CONNECT BY REGEXP_SUBSTR('?', '[^,] ', 1, level) IS NOT NULL)