I'm sorry for my overly terse reply.
Perhaps I'm being dense, but I just don't get it. Your REGEXP matches a string which starts with 1 or 2 letters, followed by 1 or 2 digits, followed by 0 or 1 letters, which tells me a short postcode would be 'OX14' or 'OX14A', but your example short postcodes are 'OX14 1' and 'OX14 2'. You add that the shortpostcodes column contains a list, but don't explicitly state what it looks like. I'd guess 'OX14 1,OX14 2', but then you throw me off with "like '%xx%'" would work if it weren't for the list.
Perhaps this would all be obvious to me if I were familiar with UK postcodes, but I'm not, so I decided to ask for clarification rather than guess. If you would clarify how to divide the short part from a postcode, and verify what the list looks like, I'd be happy to try to come up with a solution.
Michael
David Rayner wrote:
Michael, I do believe I have described what I need doing to t1.txtDevPostCode (see below) I need to truncate the t1.txtDevPostCode according to the RegExp (UK Postcodes do not unfortunately have fixed lengths) and then compare it with a list of already truncated Postcodes ie OX14 1, OX14 2 etc
If I was just comparing with a single value rather than a list I could do a like '%xx%'
From: Michael Stassen <[EMAIL PROTECTED]>
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
Shawn,
Your solution doesn't work unfortunately because I need to operate on t1.postcode before making the comparison (IT'S USUALLY the OTHER WAY ROUND) , I think this must be a generic problem, so I think an eventual solution will be interesting
zzapper (vim, cygwin, wiki & zsh)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
