CODEWITHSUNDEEP
sqloracle-databasestored-proceduresmemberof
Jaya
Jaya

Reputation: 35

Can we use LIKE operator along with MEMBER OF operator in a stored procedure?

I have an array of data using which I select rows from a table. For that I use member of operator in where clause. I want to know if we can do that same but by using Like operator along with member of operator.

When my Array consists of{Delhi, Mumbai, Kolkata} I select the rows which have these three values in their row. This is how I do that:

select ...
Into...
From xyz where city member of array;
///Receiving the array from an in parameter of the stored procedure.

And it works perfectly fine. But If my array has {Del, Mum, Kolk} //parts of the actual names How do I use this array for the same purpose, maybe using Like operator.

Create or replace zz2(ar in array_collection, c out sys_refcursor)
Is
anotherabc tablename.city%type
Begin
Open c
For
Select ABC
Into anotherabc
From tablename where city member of ar;
End zz2;

I expect the output to have all the rows which have cities starting with the alphabet/characters present in the array. Using member of operator

Upvotes: 0

Views: 96

Answers (2)

Popeye
Popeye

Reputation: 35920

There is no direct way to use LIKE with MEMBER OF.

If It is the protocol that your collection contains the first three characters of the city name then you can use substr() to match only the first three characters in MEMBER OF.

try the following thing:

DECLARE
  TYPE t_tab IS TABLE OF varchar(3);
  l_tab1 t_tab := t_tab('Del','Mom','Kol');
BEGIN
  DBMS_OUTPUT.put('Is ''Delhi'' MEMBER OF l_tab1? ');
  IF SUBSTR('Delhi',1,3) MEMBER OF l_tab1 THEN -- note the use of SUBSTR here
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/

db<>fiddle demo

Cheers!!

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31686

Something like this?

Select ABC
Into anotherabc a
From tablename WHERE EXISTS 
  ( select 1 FROM ( select column_value as city  
     FROM TABLE(ar) ) s where a.city like s.city||'%' )

Upvotes: 1

Related Questions