CODEWITHSUNDEEP
oracle-databaseplsql
user2526586
user2526586

Reputation: 1190

Alternatives of using LIKE in an Oracle IN clause

On Oracle / PL/SQL, I understand that I can't do the following with LIKE and IN clause together:

select * from booking_details where tags LIKE ('%val1%', '%val2%', 'val3%',....);

To achieve the above, I can do this:

select * from booking_details where tags LIKE '%val1%' OR tags LIKE '%val2%' OR tags LIKE '%val3%' OR ... ;

I want to apply this method to one of procedures we have.

function search_bookings(  pi_search_string in varchar2 , po_bookings out booking_tbl_typ)
    return integer as

    t_booking_table booking_tbl_typ := booking_tbl_typ();
    v_search_string varchar2(120) := pi_search_string;

begin

    declare
       result number;
    begin
        ...
    end;

    select booking_typ (
         booking_id
        ,...
    ) 
    bulk collect
        into t_booking_table
    from booking_details bd
    where
        ...
        and (bd.tags like '%'|| lower(v_search_string)||'%')
    order by booking_id, start_date;

    po_bookings := t_booking_table;
    return c_success;

exception 
    when NO_DATA_FOUND then 
        return c_success;
    when OTHERS then
        log_error_msg(...);
        v_excep_msg  :=  sqlerrm;

    return c_failure;
end search_bookings;

Users of application can basically enter anything for pi_search_string but at the moment it only works for searching for one tag. I am hoping to split pi_search_string into keywords by whitespaces and search for multiple tags in the fashion of:

select * from booking_details where tags LIKE '%keyword1%' OR tags LIKE '%keyword2%' OR tags LIKE '%keyword3%' OR ... ;

Because the query changes depending on users' input, I am not sure if this is achievable.

Upvotes: 1

Views: 290

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

You could use REGEXP_LIKE with an alternation:

SELECT *
FROM booking_details
WHERE REGEXP_LIKE(tags, 'val1|val2|val3');

Inside your stored procedure you would only need to build the pipe separated string of keywords.

Upvotes: 1

Related Questions