Pattern Matching with multiple possibilities sql

Question

I am trying to match a string against one of three possible entries. Right now I have

select * from table x 
where column1 like '%INSERT%' 
or column1 like '%Insert%' 
or column1 like '%insert%';

I'm wondering if there is a more efficient way to do this, maybe using 'in' and 'like' in conjunction?

https://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle — Honeyboy Wilson, Jul 28 '20 at 16:11
Are the three possible entries always upper/lower/mixed case variations of the same text? If the three strings are `''%INSERT%'`, `%'Insert%'` and `'%Kittens!%'`, that makes a difference. — William Robertson, Jul 28 '20 at 22:49

William Robertson · Answer 1 · 2020-07-28T18:39:46.460

2

From Oracle 12.2 (CI is case-insensitive, AI is accent-insensitive as well as case-insensitive):

where column1 collate binary_ci like '%insert%'

Earlier

where regexp_like(column1, 'insert', 'i')

or

where upper(column1) like '%INSERT%'

edited Jul 28 '20 at 18:39

answered Jul 28 '20 at 18:26

William Robertson

1 Answers1