How to filter with multiple conditions in like: Oracle SQL Developer

Question

I want to optimise the following SQL request (without creating another table):

Select * from table
where var1 like 'file_X_2022_1_001%' 
   or var1 like 'file_X_2022_1_004%' 
   or var1 like 'file_X_2022_1_006%'
   or var1 like 'file_X_2022_8_002%'
   or var1 like 'file_X_2022_8_0015%' 
   .
   .
   .
   or ... or var1 like 'file_X_2022_10_1000%';

I want something like in() where I can regroup all values in one parentheses :)

Thank you in advance!

Answer 1

You can reduce your filtering conditions in the WHERE clause to one only with REGEXP_LIKE.

SELECT * 
FROM tab
WHERE REGEXP_LIKE (var1, 'file_X_2022_[0-9]{1,2}_[0-9]?[0-9]{3}')

Pattern should be fine-tuned to exclude the values you don't allow. Values pointed in the post by you, get all caught by this regex.

Check the demo here.