Search for "whole word match" in SQL

Question

I would like to write an SQL query that searches for a keyword in a text field, but only if it is a "whole word match" (e.g. when I search for "rid", it should not match "arid", but it should match "a rid".

I am using MySQL.

Fortunately, performance is not critical in this application, and the database size and string size are both comfortably small, but I would prefer to do it in the SQL than in the PHP driving it.

Answer 1

select blah blah blah
where column like 'rid %'
   or column like '% rid'
   or column like '% rid %'
   or column =    'rid'

Answer 2

select * from table where Locate('rid ', FieldToSearch) > 0 
      or Locate(' rid', FieldToSearch) > 0

This will handle finding rid where it is preceded or followed by a space, you could extend the approach to take account of .,?! and so on, not elegant but easy.

Answer 3

This is the best answer I've come up myself with so far:

SELECT * FROM table 
WHERE keywords REGEXP '^rid[ $]' OR keywords REGEXP ' rid[ $]'

I would have simplified it to:

SELECT *
FROM table
WHERE keywords REGEXP '[^ ]rid[ $]'

but [^ ] has a special meaning of "NOT a space", rather than "line-beginning or space".

How does REGEXP compare to multiple LIKE conditions? (Not that performance matters in this app.)

Answer 4

You can use REGEXP and the [[:<:]] and [[:>:]] word-boundary markers:

SELECT *
FROM table 
WHERE keywords REGEXP '[[:<:]]rid[[:>:]]'

Answer 5

code to retrieve all the rows and columns containing the word in textbox???

Answer 6

--BELOW QUERY RETURNS SYSDATE ONLY IF "WHOLEWORD" IS PRESENT SELECT SYSDATE FROM DUAL WHERE REGEXP_LIKE ('FIND WHOLEWORD ONLY FROM THIS STRING. WHOLEWORD1 SHOULD NOT BE SELECTED ','(\s)' || '(WHOLEWORD)' || '(\s)','i');

--BELOW QUERY DO NOT RETURN SINCE "WHOLEWORD" IS PRESENT SELECT SYSDATE FROM DUAL WHERE REGEXP_LIKE ('FIND WHOLEWORD_DUP ONLY FROM THIS STRING. WHOLEWORD1 SHOULD NOT BE SELECTED ','(\s)' || '(WHOLEWORD)' || '(\s)','i');

Regards Sandeep Gaadhe [email protected]