[thelist] [MySQL] regexp in query

[Paul Bennett]

Close, this was the exact syntax that nailed it:

"SELECT  DISTINCT licencing_year
FROM licence
WHERE licencing_year
REGEXP  "^[[:digit:]]{4}/[[:digit:]]{4}$"
order by licencing_year desc"

note the extra [ ] around the [:digit:] range declaration and the 
inclusion of the exact character to match (/).
Not my first guess but it works a treat.

Have noted that there is some extra overhead using regexp for queries, 
as mysql parses the query then passes it to the regexp interpreter....

Thanks for the help

apathetic wrote:

>On Wed, 15 Dec 2004 10:11:27 +1300, Paul Bennett <Paul.Bennett at eyede.com> wrote:
>  
>
>>"SELECT  DISTINCT licencing_year
>>FROM licence
>>WHERE licencing_year
>>REGEXP  "^[:digit:]{4}+/+[:digit:]{4}$"
>>
>>and received this helpful error message from mysql:
>>"|repetition-operator operand invalid|"
>>    
>>
>
>I think the problem is that the first + is redundant because it
>immediately follows {4}.
>
>Try:
>
>"^[:digit:]{4}/{1}[:digit:]{4}$"
>
>+ = one or more of the preceeding character
>{4} = exactly four of the preceeding character
>
>Tim
>
>  
>

-- 
Paul Bennett
Web Technologies Developer
Eyede International Limited
Level 1, 5 Bouverie Street
PETONE
PO Box 31 012
Lower Hutt
Phone     +64 4 939 8764
Fax      +64 4 939 6719
www.eyede.com