SQL Group By Postcode Area

[jammiedude]

Hi i am wondering if anyone has a solution for sorting by postcode area. I wanted to be able to pick off either the First letter or first two letters and group by this.

I have figured out how to group by the first to characters of the postcode using Left(x,2) but i wanted to be able to pick out the numeric values if there are any in this subsequent string from the left fuction.

i.e. if the postcode has "L1", i just want "L", or if postcode has "CH", i want "CH". Then to be grouped by these.

Anyone have any ideas?

Thanks

[sknake]

Aren't postal codes all numeric? Where do you live

[jammiedude]

Sorry, didnt take into account american readers.

UK post codes have letters and number in them and range from 5 characters to 8, it usually takes the form 1 or 2 letters, plus 2 or 3 numbers plus 2 more letters. ie "xx00 0xx" or "x0 00xx" or "xx00x 0xx"

I really just want to identify the first 1 or 2 letters before it finds any numeric values.

Many thanks

[jammiedude]

Solved it!! But just for anyone else in the UK who is wanting the same solution without spending most of the day trying to trial and error their way through, use the following code:

GROUP BY		
CASE	WHEN ISNUMERIC(right(Left(C.Pcode,2),1)) = '0' THEN left(C.pcode,2)
		ELSE left(C.pcode,1)
		END

You must also use this CASE statement in the SELECT area, for all those who havent yet learnt aggregate's.

[sknake]

Try something like

(case when code like '[a-Z][a-Z]%' then substring 2 else substring one end)

finish the substring part. I'm on my phone and non alpha chars are hard to use