CODEWITHSUNDEEP
sqloracle11g
Miracle
Miracle

Reputation: 387

oracle sql where column in subquery with parameter

I want to test whether the values in a parameter delimited by a comma is in the column.

Value for :param_ is something like this:

Hi,Hello,World

I want my query to have a result like in this query:

SELECT col FROM tbl1 
WHERE col IN ('Hi','Hello','World');

I tried:

SELECT col FROM tbl1 
WHERE col IN (
SELECT SUBSTR( SUBSTR('''' || REPLACE(:param_, ',', ''',''') || '''', 1, 
LENGTH('''' || REPLACE(:param_, ',', ''',''') || '''') - 1), 2, 
LENGTH( SUBSTR('''' || REPLACE(:param_, ',', ''',''') || '''', 1, LENGTH(''''
|| REPLACE(:param_, ',', ''',''') || '''') - 1) ) - 1 ) 
FROM tbl1);

The subquery in the code that I tried has the output Hi','Hello','World. I removed the first and last single quote because I thought that it will have when it is inputted as a string and will be 'Hi','Hello','World' in the IN clause

Upvotes: 0

Views: 265

Answers (4)

Killer Queen
Killer Queen

Reputation: 796

DECLARE
        @string VARCHAR(100) = 'Hi,Hello,World', 
        @separator CHAR(1) = ',',
        @value VARCHAR(MAX),
        @start_position INT = 1,
        @separatorPosition INT

DECLARE @result TABLE(value VARCHAR(MAX))

SET @separatorPosition  = CHARINDEX(@separator, @string)
SET @string = @string + @separator 

IF @separatorPosition = 0  
BEGIN
    INSERT INTO @result values(@string)
END

WHILE @separatorPosition > 0
BEGIN

SET @value = SUBSTRING(@string , @start_position, @separatorPosition - @start_position)

    IF( @value <> ''  ) 
        INSERT INTO @result values(@value)

    SET @start_position = @separatorPosition + 1
    SET @separatorPosition = CHARINDEX(@separator, @string , @start_position)
END    


SELECT value from @result
SELECT col FROM tbl1 WHERE col IN (SELECT value from @result)

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could use this:

SELECT col 
FROM tbl1 
WHERE col IN (
        SELECT regexp_substr(:param_, '[^,]+', 1, LEVEL) 
        FROM dual
        CONNECT BY regexp_substr(:param_, '[^,]+', 1, LEVEL) IS NOT NULL
    );

Upvotes: 1

Fabricio Ardizon
Fabricio Ardizon

Reputation: 106

a possible solution is to put the array in a VARCHAR:

Id_list VARCHAR2(500)= 'Hi,Hello,World';

SELECT col
  FROM tbl1
 WHERE INSTR(Id_list,col) > 0

Upvotes: 0

Lihi
Lihi

Reputation: 19

maybe turn your param_ to a list and use the list in your in clause

Upvotes: 0

Related Questions