Search for top post codes
Posted on 2005-05-17
ok, I have a nvarchar(10) field to store a UK postcode, such as DK3 7VV, or LL13 5GJ
What Select quert do I need to group all postcodes by their areas, i.e. DK3 or LL13?
Say I have a data set of:
It would return
I have this code at the moment:
select COUNT(uniquekey) AS COUNT, left(addresspostcode,4) As AddressPostCode
GROUP BY addresspostcode
but it's looking at the postcode before it is truncated, so it evaluating the full string before it groups it...I want to truncate it then group it....
does this make sense?