A quick review of the REGEXP portion of the manual helped me to understand
what went wrong:
http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html
REGEXP is a comparitor, not a function. It works like "=" or ">" and the
result is a boolean value.
Were you trying to validate t1.postcode (to make sure it fit a certain
"style" of postal codes, as defined in your regular expression) AND make
sure it was one of a list of codes given to you by the user?
If you were, you needed to create your WHERE clause this way:
WHERE t1.postcode REGEXP
"^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1}"
AND t1.postcode in (<insert your comma delimited list here>)
The rule is: if you need to compare the same value against two conditions,
you have to make two distinct comparisons.
Cheers!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
zzapper
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
> cc:
Sent by: news Fax to:
<[EMAIL PROTECTED] Subject: Using REGEXP
rg>
06/29/2004 09:25
AM
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
"^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1}" in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use REGEXP
this way
qryRadius.shortpostcode contains a list of "short" postcodes OX14 1,OX14 2
etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki & zsh)
--
vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
