Search code examples
sqlsql-servert-sqlpostal-code

sql postcode space

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

Solution

  • 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)