SQL – How to remove spaces before and after database values

The following SQL can be used to remove leading and trailing spaces from a columns values.

Ideally the system that is feeding the data into the database should be doing this — but if it does not, or hasn’t in the past, this may be necessary to tidy up the records.

Notes:

  • replace table_name and table_column with your values
  • always backup a database before performing bulk changes (or at very least export the table)
  • this SQL uses WHERE conditions to reduce write operations — only values that need to be updated will be.
UPDATE table_name SET table_column = LTRIM(RTRIM(table_column))

WHERE table_column like '% ' OR table_column like ' %'