Finding Postcodes

[Rozza]

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, EC

So 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.
The postcode filed is of type nvarchar

How can I achieve the right result??

Cheers

Paul

 

[SQLBill]

Try this:

where postcode like 'E[0-9]%'

See the Books OnLine, Index tab, enter LIKE

-SQLBill

 

[cheerio]

The following should work although there may be a more efficient method.

where LEFT(postcode,2) in ('PE', 'NR', 'IP', 'OB', 'SG', 'CO', 'CM', 'SS', 'RM', 'IG','EN', 'AL', 'EC')
or
(
LEFT(postcode,2) in ('E', 'N')
and
substring(postcode,2,1) between '0' and '9'
)

 

[Rozza]

Thanks alot I'll give them a try!!

 

[jnicho02]

If you've got lots of data and do this sort of query frequently you may consider adding a column for postcode area and programmatically keeping it populated. You could then index it and ask for customers where postcode_area in ('PE','NR','IP','E',etc.) and get the results back quickly.

I believe that a LIKE statement will table scan, i.e. look through every row. Jeremy Nicholson, Director of

http://www.firstlightassociates.co.uk

a UK-based Java and Data Warehousing consultancy

 

[JamesLean]

jnicho02,

>> I believe that a LIKE statement will table scan, i.e. look through every row.

When performing a search using LIKE and an initial wildcard, eg LIKE '%e%', the optimiser cannot use an index efficiently, ie an index seek, and will most probably perform an index scan. Note the difference between this and a table scan - it is still only scanning the index rows and not the entire table's data so will still be marginally more efficient. However the ideal outcome is an index seek which it will never do.

When searching using LIKE and a non-initial wildcard, eg LIKE 'E%', it can easily make use of the index in an efficient manner - due to the fact that indexes are stored alphabetically.

Rozza,
SQLBill's query is easily the most efficient for your needs. --James

 

[NathanGriffiths]

Rozza,

This query should ensure you get all the postcodes in your list;

SELECT *
FROM <table_name>
WHERE LEFT(Postcode, PATINDEX('%[0-9]%', Postcode) -1 )
IN ('PE', 'NR', 'IP', 'OB', 'SG', 'CO', 'CM', 'SS', 'RM', 'IG', 'E', 'N', 'EN', 'AL', 'EC')


Hope that helps,

Nathan