MySQL to update Canada zipcode from 6 characters with no space to have a space

January 11, 2017

update tablename
set  zipcode =  concat(substr(zipcode,1,3) , ' ', substr(zipcode,4,3) )
where 0=0
  and zipcode regexp '^[A-Z][[:digit:]]'
  and substr(zipcode,4,1) <> ' '
  and length(zipcode) = 6