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?
Add a comment
|
3 Answers
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.
Comments
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';
Comments
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.
1 Comment
Madhu Velayudhan
how to include a table output instead of hardcoding the value in odcivarchar2list