Oracle SQL How It Doesn't Work for Word Wraps

Question

I have a table column that contains a description that looks like this:

id description -------------------------------------- 100 ... post-doctorate ... 200 ... postdoctorate ... 300 ... post doctorate ... 

I implemented a search engine where users can search for keywords, and somehow I have problems finding these words. Although I use LIKE in my WHERE , I cannot include all 3 lines above.

Query

 WHERE description LIKE '%post-doctorate%' 

I would like to be able to search all 3 of them using any options shown as a keyword.

I also looked at using SOUNDEX , but even this does not work.

note

Do not forget that I do not use parameterized queries here. I know what it is and how to use it, but it was an old project that I created.

Answer 1

Method using like :

 WHERE description LIKE '%post%doctorate%' 

But this is much more general than you want. So use regular expressions:

 WHERE REGEXP_LIKE(description, 'post[- ]?doctorate' 

Or, if you want any character to appear no more than once:

 WHERE REGEXP_LIKE(description, 'post(.)?doctorate'