Oracle SQL Like not working for hyphenated words
keyword, oracle, search, sql, sql-like
Solution
A method using `like` is:
WHERE description LIKE '%post%doctorate%'
But that is much more general than you want. So, use regular expressions:
WHERE REGEXP_LIKE(description, 'post[- ]?doctorate'
Or, if you want to allow any character to appear at most once:
WHERE REGEXP_LIKE(description, 'post(.)?doctorate'
Problem
I have a table column that holds the description which looks like this: ``` id description -------------------------------------- 100 ... post-doctorate ... 200 ... postdoctorate ... 300 ... post doctorate ... ``` I implemented a searching mechanism where users can search for keywords and somehow I'm having issues searching for these words. Even though I'm using `LIKE` in my `WHERE` clause I can't seem to include all 3 rows above. Query ``` WHERE description LIKE '%post-doctorate%' ``` I would like to be able to search all 3 of them using any of the variations illustrated as my keyword. I also looked at using `SOUNDEX` but even that doesn't work. Please Note Kindly ignore the fact that I'm not using parameterized queries in here. I'm aware of what it is and how to use it but this was an old project I created.