473,372 Members | 1,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

MATCH AGAINST HELP match exact word

I want to do the equivalent of

SELECT id from TABLE WHERE text='text'
only fast solution I found is:
SELECT id,text from TABLE WHERE MATCH(text) AGAINST('value' IN BOOLEAN
MODE) HAVING text='value'
Is there a better way to do that? Cause using having needs me to add a
column in select wich is a problem in some cases.
It has to return the EXACT WORD MATCH. so only rows containing the
exact "value" content in the text column will be returned.
thanks

Aug 9 '06 #1
4 11202

<jm********@infogt2000.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
I want to do the equivalent of

SELECT id from TABLE WHERE text='text'
only fast solution I found is:
SELECT id,text from TABLE WHERE MATCH(text) AGAINST('value' IN BOOLEAN
MODE) HAVING text='value'
Is there a better way to do that? Cause using having needs me to add a
column in select wich is a problem in some cases.
It has to return the EXACT WORD MATCH. so only rows containing the
exact "value" content in the text column will be returned.
Normally string comparisons are not case sensitive. But you can easily
force a case sensitive comparison using the BINARY keyword:

SELECT 'abc' = 'aBc';
Returns True because it does a case insensitive compare

SELECT BINARY 'abc' = 'aBc';
Returns False because BINARY forces the compare to be case sensitive.

Your example:
SELECT id
FROM TABLE {SomeTable}
WHERE BINARY 'We demand an EXACT match!' = {SomeTextField}

If your text field is char or varchar, you can also give it the field the
BINARY attribute.
This overrides the default, case insensitive, database behaviour for string
comparisons and you can leave out the BINARY keyword from the WHERE. I
don't like this myself. It is non-standard behaviour and I would rather see
the BINARY declared explicitly in the query.

Thomas Bartkus



Aug 9 '06 #2
the problem is my field is FULLTEXT

doing just equal comparision is not fast enough.

Im dealing with big tables here...

We changed a few fields to fulltext, for searches, but that field wich
is the 'username' field needs to be exactly matched for a few things.

using MATCH AGAINST is fastest way, but I dont think this allows exact
matches for some reason. So if anyone could help I'd appreciate.

Aug 10 '06 #3

"jmichel" <jm********@infogt2000.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...
the problem is my field is FULLTEXT

doing just equal comparision is not fast enough.

Im dealing with big tables here...

We changed a few fields to fulltext, for searches, but that field wich
is the 'username' field needs to be exactly matched for a few things.

using MATCH AGAINST is fastest way, but I dont think this allows exact
matches for some reason. So if anyone could help I'd appreciate.
"FULLTEXT searches are not case sensitive."
That would disqualify FULLTEXT indexing as a means to speed up the exact,
case sensitive (BINARY!) match you demand. You requirement is for a BINARY
match.

If there is a way to have your cake and eat it too -
I'm afraid I wouldn't know it!
Thomas Bartkus
Aug 10 '06 #4

Thomas Bartkus wrote:
"jmichel" <jm********@infogt2000.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...
the problem is my field is FULLTEXT

doing just equal comparision is not fast enough.

Im dealing with big tables here...

We changed a few fields to fulltext, for searches, but that field wich
is the 'username' field needs to be exactly matched for a few things.

using MATCH AGAINST is fastest way, but I dont think this allows exact
matches for some reason. So if anyone could help I'd appreciate.

"FULLTEXT searches are not case sensitive."
That would disqualify FULLTEXT indexing as a means to speed up the exact,
case sensitive (BINARY!) match you demand. You requirement is for a BINARY
match.

If there is a way to have your cake and eat it too -
I'm afraid I wouldn't know it!
Thomas Bartkus
forget about case sentitive thats not important, I just dont want the
exact word Im looking for:

if I search for "jessica" I dont want it to return "jessica-alba" I
just want "jessica" or "JesSiCa" or any other non case-sensitive match
of that word.

so far "jessica-alba" is returned and thats why I use the HAVING clause
too

Aug 10 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: vze29xhy | last post by:
I have searched the existing responses in this forum and others and could not find a solution. I have a database on MySQL server Version "4.0.13". My table structure is as follows TABLE1:MusicCD...
6
by: Mark Findlay | last post by:
I am trying to figure out how to set up my reg exp search so that the search will only match on the exact word. Here is the current problem code: Word1 = "RealPlayer.exe" Word2 = "Player.exe"...
19
by: Tom Deco | last post by:
Hi, I'm trying to use a regular expression to match a string containing a # (basically i'm looking for #include ...) I don't seem to manage to write a regular expression that matches this. ...
6
by: lawrence k | last post by:
Wierd. Go to this page: http://www.ihanuman.com/search.php and search for "yoga" This query gets run: SELECT * FROM albums WHERE MATCH(name,description) AGAINST ('yoga') ORDER BY id DESC
12
by: ross.oneill | last post by:
Hi, Is there any function in php that will match a word exactly and if it finds it, it returns true. For example if I search for "CA" strVar = "Bob is from Los Angeles CA" - return true ...
1
by: Archanak | last post by:
Hi, I want to match an exact word/phrase in mysql. I don't know weather i have to use "LIKE" syntax or "RLIKE" syntax. How do i proceed further?
5
by: nse111 | last post by:
Hey yaaaaaaa guys n gals! I want to know how I can match a whole word within a string using php. these are the results returned by the mysql query: tree:sweet:house:gate:tent...
12
by: Archanak | last post by:
Hi, I have a word like this: "Rna binding proteins" and i want to match this exact phrase. I have written code like this: $sentence="Overall, the participation of additional RNA binding...
4
by: Marek Kubica | last post by:
Hi, I am trying to get this stuff working, but I still fail. I have a format which consists of three elements: \d{4}M?-\d (4 numbers, optional M, dash, another number) EMPTY (the <EMPTYtoken)...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.