Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rjc08
Starting Member
3 Posts |
 Posted - 2008-11-13 : 10:59:06
|
| I have the following...Field IV30 1UU DD8 1XP AB21 0GL AB10 1BX AB51 4TE DD8 3JE DD8 2JW DD11 4DS AB3 8QY B16 8AA Now that I look at there are two types of postcodes one's with two letter's at the front (AB10 1BX) and one's with one letter at the front (B16 8AA). What I need is a query that gives me this result Count | PostCodeAbreviation 4 | AB 8 | BA 10 | SW 33 | B So that tells me that there are 4 records in the db with postcode that begins with AB, so on.... Can someone help me with this? Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-13 : 11:11:50
|
| [code]SELECT LEFT(PostCode,PATINDEX('%[0-9]%',PostCode)-1) AS PostCodeAbbreviation,COUNT(*) AS [Count]FROM YourTableGROUP BY LEFT(PostCode,PATINDEX('%[0-9]%',PostCode)-1)[/code] |
 |
|
|
|
|
|
postcode search