How can I use LTRIM/RTRIM to search and replace leading/trailing spaces?

To remove spaces from left/right, use LTRIM/RTRIM. What you had

UPDATE *tablename*
   SET *columnname* = LTRIM(RTRIM(*columnname*));

would have worked on ALL the rows. To minimize updates if you don't need to update, the update code is unchanged, but the LIKE expression in the WHERE clause would have been

UPDATE [tablename]
   SET [columnname] = LTRIM(RTRIM([columnname]))
 WHERE 32 in (ASCII([columname]), ASCII(REVERSE([columname])));

Note: 32 is the ascii code for the space character.


To remove spaces... please use LTRIM/RTRIM

 LTRIM(String)
 RTRIM(String)

The String parameter that is passed to the functions can be a column name, a variable, a literal string or the output of a user defined function or scalar query.

SELECT LTRIM(' spaces at start')
SELECT RTRIM(FirstName) FROM Customers

Read more: http://rockingshani.blogspot.com/p/sq.html#ixzz33SrLQ4Wi