Search code examples
sqloracle-databasesearchkeywordsql-like

Oracle SQL Like not working for hyphenated words

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.

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'