Menu
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.
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.
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.
693
The double hyphen places a single-line comment in a SQL*Plus script. The double hyphen works the same way as REMARK, except that it may be used in SQL statements and PL/SQL blocks. When used in a SQL statement or PL/SQL block, the double hyphen may be used to add trailing comments to a line.
For example the hyphen ('-') character is a reserved character meaning to exclude keywords, the column (':') character is reserved as a separator between field names and search terms.
The default behaviour of LIKE and the other comparison operators, = etc is case-sensitive.
Use braces to escape a string of characters or symbols. Everything within a set of braces in considered part of the escape sequence. When you use braces to escape a single character, the escaped character becomes a separate token in the query. Use the backslash character to escape a single character or symbol.
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
