SQL For Standardizing Postal Codes

I have a table in SQL Server that has a PostalCod开发者_高级运维e column. The data in here is not standardized - Sometimes the postal code appears is H0H 0H0 and othertimes it is H0H0H0. Using SQL only, how can I ensure each of these postal codes is standardized as H0H 0H0 ?

Well, for starters, it really depends on how it's entered into the database (i.e. you should be controlling how this is entered and managed pre-insertion). But for updating your table, you could do this to put them in the right format, with the space in between:

Update MyTable Set PostalCode=SubStr(LTrim(PostalCode),0,3) || ' ' || SubStr(RTrim(PostalCode),-3,3);

This might vary depending on the actual database engine. At the time of writing, OP had not specified which.

If you can be sure that those are the only two formats:

select left(replace(post_code,' ',''),3) + right(replace(post_code,' ',''),3)

If the case can vary wrap the result in UPPER()