sql postcode space

Question

Im trying to make it so that no matter how long the postcode is its always has a space before the last 3 characters. Sometimes i will receive postcodes and people have only typed in 4 characters so code like this :

UPDATE [DataTool].[dbo].[EH10414 Summer Events]
 SET postcode = CASE WHEN LEN(postcode) = 6 THEN STUFF(postcode, 4, 0, ' ')
                  WHEN LEN(postcode) = 7 THEN STUFF(postcode, 5, 0, ' ')
             END
 WHERE CHARINDEX(' ', postcode, 1) = 0
      AND LEN(postcode) BETWEEN 6 AND 7

Isnt really much use. I got this of this site it does work but not what im looking for. Im pretty new to SQL but in access i can use this:

Update to: Left([PostCode],Len([PostCode])-3) & " " & Right([PostCode],3)

This works perfectly but i dont know how to implement this into SQL.

any help would be great :) Thanks

What country (or countries) is your code going to be dealing with? It might be worth noting that the rules for these change significantly based on countries, and at least one country that I'm aware of (Ireland) doesn't necessarily have postal codes associated with it... — user2366842, Jul 16 '15 at 14:45

score 2 · Accepted Answer · answered Jul 16 '15 at 14:45

Your Access code will pretty much work in SQL Server:

Left(PostCode, Len(PostCode) - 3) + ' '  + Right(PostCode, 3)

I would check first that a space is not there:

(case when PostCode like '% ___' then PostCode
      else Left(PostCode, Len(PostCode) - 3) + ' '  + Right(PostCode, 3)
 end)

score 0 · Answer 2 · answered Jul 16 '15 at 15:01

One way to implement this into SQL (with some minor tweaks to get the syntax right for SQL Server) would be to create a user defined function taking an input Postcode, and returning a new version with a space added:

CREATE FUNCTION SpacePostcode  ( @inputPostcode VARCHAR(8) )
RETURNS VARCHAR(8)
BEGIN
  RETURN Left(@inputPostcode ,Len(@inputPostcode)-3) + ' ' + Right(@inputPostcode,3)
END
GO

This could then be used in a select statement to format output such as

SELECT dbo.fnSpacePostcode('EH78PP') AS postCode

or to update existing data using an update statement such as

UPDATE myTable
SET PostCode = dbo.fnSpacePostcode(PostCode)

This uses you're existing statement, but I'd agree with Gordon Linoff's comment that checking for existing spaces first would be a good idea - this function assumes no existing spaces and that you're looking to return a UK postcode with a maximum of 8 characters — EmbraCraig, Jul 16 '15 at 15:03

sql postcode space

2 Answers2