oracle sql where column in subquery with parameter

Question

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

Simply search for split comma-delimited string oracle and pick one of the answers. — dnoeth
– dnoeth, Commented May 2, 2018 at 6:47

Pham X. Bach · Accepted Answer · 2018-05-02 06:59:24Z

1

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
    );

answered May 2, 2018 at 6:59

Pham X. Bach

5,4724 gold badges32 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lihi · Accepted Answer · 2018-05-02 06:25:20Z

0

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

answered May 2, 2018 at 6:25

Lihi

196 bronze badges

1 Comment

Lihi Over a year ago

you can use functions like string_split in the where- where col in string_split(param_,',');

Fabricio Ardizon · Accepted Answer · 2018-05-02 06:29:05Z

0

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

answered May 2, 2018 at 6:29

Fabricio Ardizon

1065 bronze badges

1 Comment

dnoeth Over a year ago

This will return TRUE for 'He' or 'o,W' or ','.

Killer Queen · Accepted Answer · 2018-05-02 08:09:58Z

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)

Collectives™ on Stack Overflow

oracle sql where column in subquery with parameter

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related