I think I understand the problem now... You generate a list of "postal
prefixes" (the first portion of a full postal code) whose items may or may
not be all the same length. Then you could want to do either of two things:
1) compare a given full postal code to the list to see if matches any of
the short codes (the prefixes)
- or -
2) Scan a list of all "full codes" to see which ones are covered by your
"short codes"
Bad news: In either case, you will most likely _not_ be able to use an
index in the search
Good news: this is a solvable problem
Instead of converting your previous query (the one that generates the
"short codes") into a comma delimited list, I would put that list into a
temporary table
CREATE TEMPORARY TABLE tmpShortCodes
SELECT short_code
FROM .... (<------- this is your query that creates your "short code"
list.)
Then we can do a bulk comparison of the columns in tmpShortCodes to one or
many "full codes". What makes this simpler to achieve is the fact that you
need to match only the beginning characters of the "full code" to an entire
"short code". MySQL has 2 nearly identical functions for this: INSTR() and
LOCATE()
SELECT t1.*
FROM ytbl_development t1
INNER JOIN tmpShortCodes sc
ON INSTR(t1.txtDevPostCode, sc.short_code) =1
Like I said, it won't be fast but it should find the matches.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
zzapper
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
> cc:
Sent by: news Fax to:
<[EMAIL PROTECTED] Subject: Re: Using REGEXP
rg>
06/30/2004 04:31
AM
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
