How to use multiple values with like in sql

Question

 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?

Answer 1

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.