MySQL MATCH AGAINST digit

I am troubled here doing a Match Against in a search field.

Everything works, unless its a digit.
I would like this to be able to find a digit-only in a VARCHAR field.

3333 Fails
a333 Works

Is there some pre-formatting I can do or what is the problem?

This is just an example:

        SELECT 
                id,
                $match
        FROM 
                $cat
        
        WHERE MATCH
                ($match)
        AGAINST
                ('$query' IN BOOLEAN MODE)

that’s not really what fulltext indexing is for

sounds like you want a regular expression instead

look up REGEXP in da manual

:slight_smile:

:frowning: cries

okay hahaha ill peep REGEX (the word scares me)

hint: start of string, followed by any number of digits, followed by end of string

if you don’t get it right away, holler – those things can be tricky

I’m having the exact same problem and REGEX will not solve it. I have a zipcode database that I’m trying to search. The zipcode field is a varchar(5).

Here is a duplication of the problem as I posted it on another forum.

I’m trying to run a MATCH AGAINST query on a zip code database. The MATCH AGAINST works just fine when searching against the city or state name, but it doesn’t work with the zipcode field.

I have reset ft_min_word_len to 1, so no issue there.

Here is a short version of what my table looks like.

table zip_data {
ZipCode varchar(5),
CityName varchar(255),
StateAbbr varchar(255)
}

The table has about 100,000 records in it.

Here is my query.

select * FROM zip_data WHERE MATCH(ZipCode) AGAINST(‘778’ IN BOOLEAN MODE);

Results: empty set

If I run this query I get results.

select * from zip_data WHERE ZipCode LIKE ‘778%’;

Results: 73 rows

This is truly driving me crazy. I have reset ft_min_word_len and am running in BOOLEAN MODE. Nada. Any ideas?

Source: http://forums.mysql.com/read.php?10,365044,365044#msg-365044