Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
paul.rowling
Yak Posting Veteran
81 Posts |
 Posted - 2003-03-18 : 11:17:24
|
| Hi,I have a table which stores address data with one of the fields being a postcode field(or zip code for those of you who are american).What I want to do is to pull out customers by particular postcode. The following is a list of postcodes that I want to select customers by, but they are only the beginning of the postcode.PE, NR, IP, OB, SG, CO, CM, SS, RM, IG, E, N, EN, AL, ECSo I want to pull out customers who's postcodes start with the above. The problem that I have is that the entries in the list above that are only a single character. This means that we are looking for postcodes that start for example with an E and are then immediately followed by a number. The other postcodes start with two letters and are then followed by a number. If I did a search for "like E%" this would return anything starting with E and not those which are E followed by a number e.g E10 SLR, E25 XLT etc.How can I achieve the right result??CheersPaul |
|
|
paul.rowling
Yak Posting Veteran
81 Posts |
Posted - 2003-03-18 : 11:25:55
|
| I forgot to mention that the postcode field is of type nvarchar. |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-03-18 : 11:34:37
|
| LEFT(postcode, PATINDEX('%[^A-Z]%', postcode+' ')-1)Why is it nvarchar? UK postcodes only contain [A-Z 0-9]...actually, this is overkill: if all your postcodes are properly formed there will be a digit after the E, so LIKE 'E[0-9]%' should be fine.Edited by - Arnold Fribble on 03/18/2003 11:41:59 |
|
|
|
paul.rowling
Yak Posting Veteran
81 Posts |
Posted - 2003-03-18 : 11:51:24
|
| Hi Arnold, thanks for your post.Would my query look like this:select customer_idfrom customerswhere postcode = 'pe%'or postcode = 'nr%'or postcode = 'ip%'or postcode like 'e[0-9]%'......etcCheersPaul |
|
|
|
paul.rowling
Yak Posting Veteran
81 Posts |
Posted - 2003-03-18 : 13:29:13
|
| Hi,Got it sorted now with the help of Arnold's post!CheersPaul |
|
|
|
|
|
|
|
|
Finding PostCodes (Zip Codes)