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

hi,
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

Try



You may need to the tweak the regular expression string a little to get it right, but that should get you 'camel' at the beginning of the string.

Madpawn's usage of Regular Expressions is inspiring (seriously!)
But I don't understand why select * from tablename where textfield like '%camel%' doesn't work?

Oh wait, you want Camel_something, but not Something_camel?
The word has to start with what you're searching... [I think i get it, but can you still clarify]

I think Madpawn's suggestion won'd necessarily work. He's looking to find where camel is the beginning of the entire string, not individual words.

There is a function for start of word, but I think its only implemented by Unix commands... look it up though... I believe the expression would be: \<(Camel|camel)

Yeah, I may have misunderstood what you're asking for.

Like MadCowDzz said, my query is only for finding results where the string starts with 'camel'.

Madpawn, do you know a lot about Regular Expressions?
Inparticular: anything about the \< operator?

I had the understanding that a backslash was an escape character, and in doing this it would essentially escape the lessthan symbol. However, I read in a couple Unix man pages that using \< will indicate that the item must be at the beginning of a word. The only place I've seen this used is in these Unix commands, and none of the online Regex checkers seem to use such a technique...

Perhaps anyone else knows?

[btw, i love regular expressions!]

Let's say I have a love/hate relationship with 'em.

I've never seen anything about a backslash working as a word boundary character, though. Seems like it's got enough to do as an escape char. I wonder if it wasn't meant to be something like php's \b<, which indicates a word boundary followed by a less than sign.

Even if it can work alone as a word indicator, I'd think it'd have to be escaped, so you'd have to do \\<. But know I'm just guessing, so I'll shut up.

Does \b work in MySQL?
in which case, wouldn't \b(Camel|camel) be a possible solution?
[i'm not in a case to test this, if no one reponds i'll test when i get home]

lol, and I agree with your love/hate relationship... It seems there's not quite a set of standards... each program's implementation of regex has its own minor differences on a few expressions...

I'm not sure about \b -- I'd have to look it up, but I'm gonna just guess it doesn't. It does have this construct I'd forgotten about though: [[:<:]], which may be related to the \< you read about.

So



may do the trick.

Checking the MySQL Documentation, I think [[:<:]] is the equavalent of \< that I found earlier...

the thing I found is mentioned in Oreilly's Mastering Regular Expressions book... Which i think is the same as \b... but don't quote me on that...

I think we've answered the problem... I know I'm satisfied, and I can't wait to get home and try this on my own... I hope our discussion helps vani as well...

thanks Madpawn!

Hi all!


Ever heard of the MATCH function in MySQL?
It was meant for these kinda actions.
Check out http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
Works like this:
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

Should do the trick for you!

Angela

Match is good, but does not differentiate between words starting with camel, or ending on camel

I would just add a space between the percent sign and the string:



and to fix the case-sensitivity:

Adding a space would not return a match if Camel is the first word in the string...
I stand by Regular Expressions =)
Although these are all great suggestions! [keep 'em coming]

I can see cases where one might need MATCH...

Unless I'm mistaken, MATCH syntax only works when you have a fulltext index in your table.

Nope, you are right there.
It will only work with a fulltext index.

If you want to search the exact string in MySQL, i.e. case-sensitivity, use 'binary' as in: