I am running a SQL query looking for exact words or phrases (user decides at run time). So the request might be to look for the phrase "plays catch"
The SQL query that I create would be similar to this:
SELECT *
FROM NOTES
WHERE DETAIL LIKE "plays catch %"
OR DETAIL LIKE "% plays catch %";
Because the user will be looking for an exact phrase I put the space after "catch" and before the "%" because I want to avoid a similar but different word such as "Catch-up".
So the problem comes with handling punctuation such as a period or a comma.
My query now unfortunately will exclude a note with the detail of
John plays catch.
but will include
John plays catch with his dog.
Does anybody know of a simple way to have basic punctuation ignored in a SQL query?
BTW, this is my first post on your forum so happy to accept guidance if I've mis-stepped in this post.
If your database supports regular expressions, then you can handle it that way. For instance:
SELECT *
FROM NOTES
WHERE ' ' || DETAIL REGEXP ' plays catch[ ,.!?]' ;
(|| is the standard operator for string concatenation, although some databases do not support it.)
Or you could use the word delimiter ('\w') if that is appropriate.
If you database does not, you can use REPLACE():
SELECT *
FROM NOTES
WHERE REPLACE(REPLACE(' ' || DETAIL, ',', ' '), '.', ' ') LIKE '% plays catch %' ;