Formatting UK Postcodes 4

[monkeybarfan]

I have a query and the end user needs to have the postcode field in the dataset in either this format 'AB1 1AB' or this format 'AB12 1AB'

(For the non Brits, UK postcode can be 7 or 8 characters)

Currently the database has them stored as AB11AB or AB121AB i.e. no space separating the high and lower level geographical codes. So I need to insert the spaces in my query (but leave the original data source intact)

I'm guessing a CASE statement and I can probably get away with working back 3 characters from the right (-3?) in terms of where to insert the space but can anyone help me with the code for this?

Also, there are some NULL values but I guess they can just be ignored. There are some nonsense fields where the user/chimp has keyed in the town name instead of the postcode so I may need to clean up some of this too but I can come to that later (Maybe if the length <> 6 or 7 then do not adjust formatting or something).

 

[ca8msm]

You'd be better off doing this in the client application rather than the sql statement.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[monkeybarfan]

How do you mean?

I cannot alter the original database

 

[ca8msm]

I mean you should do this on the client. How are you displaying this data to the user?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[PDreyer]

select case len(pcode) 
when 6 then left(pcode,3)+' '+right(pcode,3)
when 7 then left(pcode,4)+' '+right(pcode,3)
else pcode
end
from (
select pcode=
'AB11AB'  union all select 
'AB121AB' union all select 
'UK'      )t

 

[monkeybarfan]

I'm writing a query on SQL Server 2005 to export a dataset to a CSV text file, in order to send the data to an external client, as our dataset is forming part of a bigger national project and they have strict requirements that it must be in this format.

You think I should just do a find and replace?

 

[monkeybarfan]

Thanks PDreyer I'll try that first and foremost

 

[HarleyQuinn]

monkeybarfan, don't forget you'll also have to add in a statement for the 6 digit postcodes e.g. L1 XXX

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PDreyer]

when 6 then [COLOR=red]rtrim([/color]left(pcode,3)[COLOR=red])[/color]+' '+right(pcode,3)

 

[HarleyQuinn]

You've lost me, why would you trim data that contains no spaces?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PDreyer]

Else e.g. the left 3 chars of L 1 <space> will be double spaced if you concatenate <space> and the right 3 chars

 

[HarleyQuinn]

I thought that was what you meant, however the base data doesn't have spaces in as that's what the OP is trying to achieve. If we just extend out your original answer to include these smaller postcodes then we're covered on all bases:

when 5 then left(pcode,2)+' '+right(pcode,3)

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[earthandfire]

Postcodes have the format:

AN NAA
ANN NAA
AAN NAA
AANN NAA
ANA NAA
AANA NAA

and the special AAA NAA (for the Girobank postcode only)

where A = Alpha and N = Numeric


Therefore, just testing the length is not sufficient.

Although all letters (for the A parts) are not used, and in fact, some are marked by Royal Mail as being unavailable, the following is more precise as it rejects values which don't have the correct pattern. I haven't include Girobank, you can add that if you need it.

select 
	case when len(ltrim(rtrim(postcode))) between 5 and 7 then
		case when 
			patindex('[a-z][1-9][0-9][a-z][a-z]',ltrim(rtrim(postcode))) = 1 or
			patindex('[a-z][1-9][0-9][0-9][a-z][a-z]',ltrim(rtrim(postcode))) = 1 or
			patindex('[a-z][a-z][1-9][0-9][a-z][a-z]',ltrim(rtrim(postcode))) = 1 or
			patindex('[a-z][a-z][1-9][0-9][0-9][a-z][a-z]',ltrim(rtrim(postcode))) = 1 or
			patindex('[a-z][1-9][a-z][0-9][a-z][a-z]',ltrim(rtrim(postcode))) = 1 or
			patindex('[a-z][a-z][1-9][a-z][0-9][a-z][a-z]',ltrim(rtrim(postcode))) = 1 or
			patindex('[a-z][1-9][1-9][a-z][a-z]',ltrim(rtrim(postcode))) = 1
		then
			left(ltrim(rtrim(postcode)),len(ltrim(rtrim(postcode)))-3) + ' ' + right(ltrim(rtrim(postcode)),3)
		else
			''
		end
	else 
		''
	end as FormattedPostcode

Hope this helps.

 

[HarleyQuinn]

Have a star for going the extra mile E&F

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[monkeybarfan]

Have an extra big gold star if you can see a way around where the user has input a damn city name in the postcode field!!!

 

[ca8msm]

Have an extra big gold star if you can see a way around where the user has input a damn city name in the postcode field!!!

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[HarleyQuinn]

What do you want to return if that's the case?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[earthandfire]

If it fails the pattern tests, it can't be a postcode. You could run a query to pull out those that fail. Obviously someone would then have to look at them to see if the columns had been populated incorrectly.

Thanks HarlyQuinn. A few years ago I had to do some address book work and as a result got into RegEx in VB.Net to handle postcodes.

 

[monkeybarfan]

I don't think I'll bother trying to cleanse those user errors.

There are all kinds of bizarre stuff, like
Ffynonwen
14cv214ex
12PenybrynRd

However, after scrolling through randomly these are probably less than 1% of all records, maybe even 0.1% so I think we can live with these