CODEWITHSUNDEEP
sqloracle-database
Madhu Velayudhan
Madhu Velayudhan

Reputation: 59

How to use multiple values with like in sql

 select * from user_table where name in ('123%','test%','dummy%')

How to ensure that this where clause is not an exact match, but a like condition?

Upvotes: 4

Views: 52952

Answers (3)

Husqvik
Husqvik

Reputation: 5809

To not lose indexed access to rows in Oracle a table collection expression can be used:

SELECT
    *
FROM
    user_table
    JOIN (SELECT column_value filter 
          FROM table(sys.odcivarchar2list('dummy%', '123%', 'test%'))
    ) ON user_table.name LIKE filter

The filter expressions must be distinct otherwise you get the same rows from user_table multiple times.

Upvotes: 3

Mohammed Safeer
Mohammed Safeer

Reputation: 21535

Use like this,

select * 
from user_table 
where name LIKE '123%' 
OR name LIKE 'test%' 
OR name Like 'dummy%';

another option in MySQL

select * from user_table where name REGEXP '^123|^test|^dummy';

Upvotes: 5

trincot
trincot

Reputation: 350107

In Oracle you can use regexp_like as follows:

select *
from   user_table
where  regexp_like (name, '^(123|test|dummy)')

The caret (^) requires that the match is at the start of name, and the pipe | acts as an OR.

Be careful though, because with regular expressions you almost certainly lose the benefit of an index that might exist on name.

Upvotes: 9

Related Questions