Ad:

MS SQL Discussion Thread
Marked Solved
Views: 1274

Aug 19th, 2009

SQL Group By Postcode Area

Expand Post »

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

Similar Threads

SQL Data into Textbox or Listbox in C#
Install : SQL Server Business Intelligence Development Studio in MS SQL
error "Could not open a connection to SQL Server" in MS SQL
Populating a datagrid with dynamic SQL in ASP.NET
sql server beginner in MS SQL

jammiedude

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

32 posts
since Jun 2008

Permalink

Aug 19th, 2009

Re: SQL Group By Postcode Area

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

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Aug 19th, 2009

Re: SQL Group By Postcode Area

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

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

32 posts
since Jun 2008

Permalink

Aug 19th, 2009

Re: SQL Group By Postcode Area

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:

SQL Syntax (Toggle Plain Text)
GROUP BY		
CASE	WHEN ISNUMERIC(RIGHT(LEFT(C.Pcode,2),1)) = '0' THEN LEFT(C.pcode,2)
		ELSE LEFT(C.pcode,1)
		END
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.

jammiedude

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

32 posts
since Jun 2008

Permalink

Aug 19th, 2009

Re: SQL Group By Postcode Area

Try something like

sql Syntax (Toggle Plain Text)
(CASE WHEN code LIKE '[a-Z][a-Z]%' THEN SUBSTRING 2 ELSE SUBSTRING one END)
(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

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: auto increment the record value in database when the button clicks

Next Thread in MS SQL Forum Timeline: SQL QUERY FOR THIS ? Please Help to solve this problem .

DaniWeb Message

Cancel Changes

User Name
Password