In all of your examples so far, the short postcode ends with the first
character after the space. If that is true for all short postcodes, we
could take the portion of the full postcode up to the first character after
the space, then compare that to the list. I think that's what you were
hoping to do with the regexp. Since your list is comma-separated, we can
use FIND_IN_SET to compare the portion of the postcode to the list. So,
SELECT * FROM ytbl_development AS t1
WHERE FIND_IN_SET(LEFT(t1.txtDevPostCode,LOCATE(' ',t1.txtDevPostCode)+1),
'OX14 1','OX14 2','SE1 1');
This won't use an index on txtDevPostCode, so it will require a full table scan.
Michael
zzapper wrote:
Michael
Ignoring my attempt at a query, I'll restate the problem
T1.devtxtpostcode contains full UK Postcodes eg OX14 5RA, OX14 5BH, Se1 1AH, etc
I want to check if a particular postcode is within a list of postcode areas, these postcode areas
are naturally shorter ie ox14 5,ox14 6 etc. So I need to write a query that will check if OX14 5RA
matches one of the postcode areas
If UK Postcodes had a fixed structure I could write
select * from ytbl_development as t1
where mid(t1.txtDevPostCode,1,5) in ('ox14 1','ox14 2','se1 1')
unfortunately I can't use mid as I can't guarantee that the length of a short postcode
is 5 chars
How would you solve this problem
(The list of short Area Postcodes is generated by an earlier query)
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]
