MySQL search exact word with $ in word

Question

I've been trying to find exact words and my research isn't helping me.

Solutions I've found:

$query .= "WHERE text REGEXP '[[:<:]]($word)[[:>:]]'";
// OR
$query .= "WHERE MATCH(text) AGAINST('$word') ";

but neither are returning my matches.

I'm searching for stock symbols in my db (eg $aapl). And using LIKE '%$word%' will return $bac if you search for $ba.

post the "SHOW CREATE TABLE" output, please. – randomx Oct 22 '10 at 17:18 — randomx, Oct 22 '10 at 17:18

score 1 · Answer 1 · answered Oct 22 '10 at 17:17

1

You will want to use MySql Full Text Search functions to accomplish what you describe above.

Enjoy!

answered Oct 22 '10 at 17:17

Doug

5,268
24
31

I read that article previous to posting this question. I have enabled full text, but I'm not sure what I'm doing wrong. – Jacksonkr Oct 22 '10 at 17:22
1

@Jackson - if you are looking for a string literal then you need to use double quotes around the search term you are attempting to search for. Also I believe the only table type that supports full text search is myisam and you do need to add full text indexes to the columns you are searching on as well. – Doug Oct 22 '10 at 17:24
I put in the string literal (thanks doug). I'm getting an error when I use the dollar sign though. Here's what I have: $query .= "WHERE MATCH(text) AGAINST(\"\$bac\") "; – Jacksonkr Oct 22 '10 at 17:53

score 0 · Accepted Answer · answered Oct 23 '10 at 16:32

MATCH AGAINST doesn't work exactly how you'd think it would.

This can be summarized by Casey Fulton as "...FULLTEXT searches only [return] anything if the number of results is less than 50% of the total table size..."

So instead, I'm searching for all the LIKEs and then filtering out exact words by a REGEX. I'm doing this to cut down on the process load for REGEX.

The following is my solution:

$likerows = "(SELECT * FROM `tweets` WHERE text LIKE '%$q%') AS likerows ";
$regexrows = "(SELECT * FROM $likerows WHERE text REGEXP('^.* $q .*$')) AS regexrows ";
$query = "SELECT * FROM $regexrows ";

I put spaces around the $q because I want to match a sentence-structured word (which is preceeded and followed by a space).

Also, I'm searching for "words" that start with $. This was an issue and here's what I did to solve that problem:

if(substr($q, 0, 1) == '$') $q = '\\\\' . $q; // you have to escape the backslash that escapes the $ -- it's nuts.

Best of luck.

MySQL search exact word with $ in word

2 Answers2

Linked