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

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

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

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.