MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMySQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old October 25th, 2004, 05:12 AM
vani vani is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2004
Posts: 8 vani User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 42 m 42 sec
Reputation Power: 0
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

Reply With Quote
  #2  
Old December 4th, 2004, 11:10 AM
Madpawn Madpawn is offline
My beat is correct.
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Posts: 339 Madpawn User rank is Private First Class (20 - 50 Reputation Level)Madpawn User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 10
Try

Code:
SELECT
 *
FROM
 table
WHERE
 fieldname REGEXP '^(Camel|camel)'


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.

Reply With Quote
  #3  
Old December 6th, 2004, 08:51 AM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
 
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,886 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 19 m 35 sec
Reputation Power: 14
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)

Reply With Quote
  #4  
Old December 6th, 2004, 09:54 AM
Madpawn Madpawn is offline
My beat is correct.
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Posts: 339 Madpawn User rank is Private First Class (20 - 50 Reputation Level)Madpawn User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 10
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'.

Reply With Quote
  #5  
Old December 6th, 2004, 12:55 PM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
 
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,886 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 19 m 35 sec
Reputation Power: 14
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!]

Reply With Quote
  #6  
Old December 6th, 2004, 01:34 PM
Madpawn Madpawn is offline
My beat is correct.
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Posts: 339 Madpawn User rank is Private First Class (20 - 50 Reputation Level)Madpawn User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 10
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.

Reply With Quote
  #7  
Old December 6th, 2004, 02:05 PM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
 
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,886 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 19 m 35 sec
Reputation Power: 14
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...

Reply With Quote
  #8  
Old December 6th, 2004, 03:19 PM
Madpawn Madpawn is offline
My beat is correct.
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Posts: 339 Madpawn User rank is Private First Class (20 - 50 Reputation Level)Madpawn User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 10
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

Code:
 SELECT
  *
 FROM
  table
 WHERE
  fieldname REGEXP '[[:<:]](Camel|camel)'
 


may do the trick.

Reply With Quote
  #9  
Old December 6th, 2004, 03:36 PM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
 
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,886 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 19 m 35 sec
Reputation Power: 14
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!

Reply With Quote
  #10  
Old December 10th, 2004, 05:32 AM
Angela Angela is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Location: Netherlands
Posts: 11 Angela User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Cool

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

Reply With Quote
  #11  
Old December 10th, 2004, 07:54 AM
Itsacon's Avatar
Itsacon Itsacon is offline
Command Line Warrior
Click here for more information
 
Join Date: Aug 2004
Location: Sector ZZ9 Plural Z Alpha
Posts: 1,030 Itsacon User rank is Lance Corporal (50 - 100 Reputation Level)Itsacon User rank is Lance Corporal (50 - 100 Reputation Level)Itsacon User rank is Lance Corporal (50 - 100 Reputation Level)  Folding Points: 2966446 Folding Title: Super Ultimate Folder - Level 6Folding Points: 2966446 Folding Title: Super Ultimate Folder - Level 6Folding Points: 2966446 Folding Title: Super Ultimate Folder - Level 6Folding Points: 2966446 Folding Title: Super Ultimate Folder - Level 6Folding Points: 2966446 Folding Title: Super Ultimate Folder - Level 6Folding Points: 2966446 Folding Title: Super Ultimate Folder - Level 6Folding Points: 2966446 Folding Title: Super Ultimate Folder - Level 6Folding Points: 2966446 Folding Title: Super Ultimate Folder - Level 6Folding Points: 2966446 Folding Title: Super Ultimate Folder - Level 6Folding Points: 2966446 Folding Title: Super Ultimate Folder - Level 6
Time spent in forums: 1 Week 12 h 39 m 36 sec
Reputation Power: 12
Send a message via ICQ to Itsacon
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:

Code:
WHERE textfield LIKE "% Camel%"


and to fix the case-sensitivity:

Code:
WHERE LOWER(textfield) LIKE "% camel%"

Reply With Quote
  #12  
Old December 10th, 2004, 08:03 AM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
 
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,886 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 19 m 35 sec
Reputation Power: 14
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...

Reply With Quote
  #13  
Old December 11th, 2004, 12:20 AM
Madpawn Madpawn is offline
My beat is correct.
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Posts: 339 Madpawn User rank is Private First Class (20 - 50 Reputation Level)Madpawn User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 10
Unless I'm mistaken, MATCH syntax only works when you have a fulltext index in your table.

Reply With Quote
  #14  
Old December 12th, 2004, 05:30 AM
Angela Angela is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2004
Location: Netherlands
Posts: 11 Angela User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Lightbulb

Quote:
Originally Posted by Madpawn
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:
Code:
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';
or 
mysql> Select * FROM pet where name like BINARY 'Fluffy';

Reply With Quote
  #15  
Old January 14th, 2013, 10:26 AM
fab5freddy fab5freddy is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2013
Posts: 1 fab5freddy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 m 25 sec
Reputation Power: 0
Just saying thanks

I just wanted to say that this snippet helped me out a great deal of trying to search through a string. I am fairly new to programming and was wondering if you could point me in the direction of a few good books / articles about regular expressions. I have always struggled with them. Once again thanks for the help.

Quote:
Originally Posted by Madpawn
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

Code:
 SELECT
  *
 FROM
  table
 WHERE
  fieldname REGEXP '[[:<:]](Camel|camel)'
 


may do the trick.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMySQL Development > search a word in a string for exact match / even startingwith the word is not working


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap