Mysql RegExp question selecting from a list of codes

I am trying to match a l开发者_StackOverflow中文版ist of motorcycle models to a series of ebay codes for listing motorcycles in ebay.

So we get a motorcycle model name that will be something like:

XL883C Sportster where the manufacturer is Harley Davidson

I have a list of ebay codes that look like this

MB-100-0    Other
MB-100-1            883
MB-100-2            1000
MB-100-3            1130
MB-100-4            1200
MB-100-5            1340
MB-100-6            1450
MB-100-7            Dyna
MB-100-8            Electra
MB-100-9            FLHR
MB-100-10   FLHT
MB-100-11   FLSTC
MB-100-12   FLSTR
MB-100-13   FXCW
MB-100-14   FXSTB
MB-100-15   Softail
MB-100-16   Sportster
MB-100-17   Touring
MB-100-18   VRSCAW
MB-100-19   VRSCD
MB-100-20   VRSCR

So I want to match the model name against the list above using a regExp pattern.

I have tried the following code:

SELECT modelID FROM tblEbayModelCodes WHERE
LOWER(makeName) = 'harley-davidson' AND fnmodel REGEXP '[883|1000|1130|1200|1340|1450|Dyna|Electra|FLHR|FLHT|FLSTC|FLSTR|FXCW|FXSTB|Softail|Sportster|Touring|VRSCAW|VRSCD|VRSCR].*' LIMIT 1

however when I run the query I would expect the code to match on either MB-100-1 for 883 or MB-100-16 for Sportster but when I run it the query returns MB-100-0 for Other.

I am guessing that I have the pattern incorrect, so can anybody suggest what I might need to do to correct this?

Many thanks Graham

[chars] matches any of the characters 'c','h','a','r','s'

So by giving it such a long list, it will inevitably match just the first item (single character)

Try this instead

LOWER(makeName) = 'harley-davidson' AND fnmodel REGEXP '(883|1000|1130|1200|1340|1450|Dyna|Electra|FLHR|FLHT|FLSTC|FLSTR|FXCW|FXSTB|Softail|Sportster|Touring|VRSCAW|VRSCD|VRSCR).*' LIMIT 1

You might also consider not using REGEX and using FIND_IN_SET instead.

Not really fully tested, but it should be something like this:

REGEXP '^MB-[0-9]+-[0-9]+[[:space:]]+(883|1000|1130|1200|1340|1450|Dyna|Electra|FLHR|FLHT|FLSTC|FLSTR|FXCW|FXSTB|Softail|Sportster|Touring|VRSCAW|VRSCD|VRSCR)$'

In detail:

^MB-            Starts with MB-
[0-9]+          One or more digits
-               Dash
[0-9]+          One or more digits
[[:space:]]+    One or more white space
(883|1000|...)$ Ends with one of these

Here's the reference for the regexp dialect spoken by MySQL:

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Answer to comment:

If you want to match the Sportster row them remove all other conditions. And you may not even need regular expressions:

WHERE fnmodel LIKE '% Sportster'