13 messages in com.mysql.lists.mysqlRe: Using REGEXP
FromSent OnAttachments
zzapper29 Jun 2004 06:24 
zzapper29 Jun 2004 09:43 
SGr...@unimin.com29 Jun 2004 11:47 
Wesley Furgiuele29 Jun 2004 12:12 
zzapper29 Jun 2004 12:36 
Michael Stassen29 Jun 2004 13:23 
Michael Stassen29 Jun 2004 21:49 
zzapper30 Jun 2004 01:31 
SGr...@unimin.com30 Jun 2004 06:10 
Michael Stassen30 Jun 2004 07:58 
Harald Fuchs30 Jun 2004 08:44 
SGr...@unimin.com30 Jun 2004 08:59 
zzapper01 Jul 2004 01:54 
Subject:Re: Using REGEXP
From:Michael Stassen (Mich@verizon.net)
Date:06/30/2004 07:58:58 AM
List:com.mysql.lists.mysql

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