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 fin开发者_Go百科d 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.

---

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."%'
)

---

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..

---

"%" 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" .