CODEWITHSUNDEEP
phpmysqlsearch
Dharm Shankar
Dharm Shankar

Reputation: 53

Mysql Text Search Issues

I am trying to make exact search but getting problem when search with "Sonal", I am getting result from the record has "Sonal" & "personal". When I remove % before var name then getting the result which record start from "Sonal" and doesn't find that text has "Sonal" anywhere.

Here is the query:

AND (
       lower(document_text) LIKE '%".$search_name2."%' 
    OR lower(customdoc_name) LIKE '%".$search_name2."%' 
    OR lower(doc_tags) LIKE '%".$search_name2."%'
)

Rec. 1 - Personal Certificate
Rec. 2 - Sonal Certificate.

I want only 2nd record return when search with "Sonal".

I am not using FULLTEXT Field.

Upvotes: 1

Views: 98

Answers (3)

sankar.suda
sankar.suda

Reputation: 1147

Use REGEXP try following..

AND (
       lower(document_text) REGEXP '[[:<:]]".$search_name2."[[:>:]]' 
    OR lower(customdoc_name) REGEXP '[[:<:]]".$search_name2."[[:>:]]' 
    OR lower(doc_tags) REGEXP '[[:<:]]".$search_name2."[[:>:]]' 
)

Performance wise not good.. but you will get exact results..

Upvotes: 1

Nimit Dudani
Nimit Dudani

Reputation: 4860

this may help,

add space back of string or front and back or front only. by this you will get result with "sonal" in case of beginning of string or middle of string or end of string.

AND (
       lower(document_text) LIKE '%".$search_name2." %' 
    OR lower(customdoc_name) LIKE '% ".$search_name2." %' 
    OR lower(doc_tags) LIKE ' %".$search_name2."%'
)

Upvotes: 2

DhruvPathak
DhruvPathak

Reputation: 43235

"%" operator behaves this way only.

It is a wildcard character meaning ANY STRING. So

  1. "%HELL%" will match "OT*HELL*O" , "HELL" , "HELLO" , "THE_HELL" etc
  2. "HELL%" will match only words starting with HELL like "HELL","HELLO","HELLOWW", but not "OTHELLO".
  3. "%HELL" will match words ending with HELL like "THE_HELL", but not "HELLO" .

Upvotes: 0

Related Questions