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

Question

I'm attempting to remove leading and trailing spaces from an NVARCHAR(MAX) column that contains price information (using NVARCHAR due to data importing from multiple operating systems with odd characters).

The leading and following spaces from static prices can now be removed using a t-SQL statement. I'm at a loss for how to use this identical command to eliminate all pricing, though.

The static script I used to eliminate particular pricing is shown here:

UPDATE *tablename* set *columnname* = LTRIM(RTRIM(2.50)) WHERE cost = '2.50 ';

Here's what I've tried to remove all the trailing spaces:

UPDATE *tablename* set *columnname* LIKE LTRIM(RTRIM('[.]')) WHERE cost LIKE '[.] ';

I've also experimented with various the% for random character variations, but I'm currently at a loss for ideas.

I want to remove all the leading and following spaces from each cell in this column with just one straightforward command, without changing any of the column data itself.

Answer 1

Use LTRIM/RTRIM to eliminate spaces from the left and right. What you had:

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

Would have worked on ALL the rows. The update code remains the same to reduce updates if you don't need to update, 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])));

I hope this helps you.