CODEWITHSUNDEEP
phpcakephp-2.0
Sunat Sharma
Sunat Sharma

Reputation: 13

search a word in a string for exact match / even startingwith the word is not working

I have a id and a text field.

I need to search a word with exact match or starting with the word in a string with single mysql query.

E.g select * from tablename where textfield like "%cam%. This will return all the text id which cam is found anywhere in the single .

but i need to get the result that can be queried by splitting single words in a sentence.

id text

1 Camel_walk.
2 camel does drink water.
3 does Camel_store water.
4 In deset people can find_camel
5 this will not return

when i query for select * from tablename where textfield like "%camel%. return first 1,2,3,4

but i need to get the id in which the word start with camel

1)select * from tablename where textfield like "Camel%"
return 1,3 ( which is not working for me)
or even
2)select * from tablename where textfield like "%Camel"
return 4

These two queries is not working Can some one help

Thanks

Upvotes: 1

Views: 812

Answers (4)

Yogesh Saroya
Yogesh Saroya

Reputation: 1515

user match against

$conditions[]=array("MATCH(MODEL_name.fieldname) AGAINST('$text' IN BOOLEAN MODE)" );

Upvotes: 0

Fr0zenFyr
Fr0zenFyr

Reputation: 1939

When you use a camel(proper) case in the SQL like query, the string will find case-sensitive match of the string, however, "camel" and "CAMEL" will look for camel/CAMEL/Camel...so on (case-insensitive match)

select id from tablename where text like "%Camel%" will return 1,3.

select id from tablename where text like "%camel" will return 4.

select id from tablename where text like "%camel%" will return 1,2,3,4.

select id from tablename where text like "Camel%" will return 1.

select id from tablename where text like "camel%" will return 1,2.

select id from tablename where text like "camel %" (space between "camel" and "%") will return 2.

Notice the difference in the case of the string.

Upvotes: 2

Victor
Victor

Reputation: 666

This should work for all cases

SELECT id
  from tablename
 WHERE textfield like '%[^A-z^0-9]Camel%' -- In the middle of a sentence
    OR textfield like 'Camel%'            -- At the beginning of a sentence

Upvotes: 0

Hanky Panky
Hanky Panky

Reputation: 46900

select * from tablename where textfield like "Camel%" or textfield like ' %Camel'

Notice the space before % in second condition. These two conditions will match when any word starts with Camel in your string, doesn't matter if it is the first word or not.

Upvotes: 0

Related Questions