How to use LTRIM / RTRIM to find and replace leading / trailing spaces?

Question

I am trying to clear leading and trailing spaces from an NVARCHAR (MAX) column filled with prices (using NVARCHAR due to importing data from several operating systems with odd characters).

At this point, I have a t-sql command that can remove leading / trailing spaces from static prices. However, when it comes to using the same command to remove all prices, I'm at a standstill.

Here's the static script I used to remove a specific price:

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

Here I tried to remove all trailing spaces:

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

I also tried different% variations for random characters, but at this moment I spin my wheels.

What I hope to achieve is to run one simple command that removes all the leading and trailing spaces in each cell of this column without changing any actual column data.

Answer 1

To remove spaces from left to right, use LTRIM / RTRIM. What did you have

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

would work on ALL lines. To minimize updates, if you do not need to update, the update code does not change, but the LIKE expression in the WHERE clause would be

 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.

Answer 2

To remove spaces ... use ltrim / rtrim LTRIM (String) RTRIM (String) The String parameter passed to the function can be a column name, variable, literal string, or the output of a user-defined function or scalar query.

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

More details: http://rockingshani.blogspot.com/p/sq.html#ixzz33SrLQ4Wi

Answer 3

LTrim Function and RTrim Function :

• The LTrim function to remove leading spaces and the RTrim function to remove trailing spaces from a string variable.

• It uses the Trim function to remove both types of spaces .

    select LTRIM(RTRIM(' SQL Server ')) 

  output:

    SQL Server 

Answer 4

 SELECT RTRIM(' Author ') AS Name; 

The output will be without any trailing spaces.

Name ------ 'Author

Answer 5

I understand that this question has been asked for SQL Server 2012, but if the same scenario is for SQL Server 2017 or SQL Azure, you can use Trim directly, as shown below:

 UPDATE *tablename* SET *columnname* = trim(*columnname*); 

Answer 6

The LTrim function to remove leading spaces and the RTrim function to remove trailing spaces from a string variable. It uses the Trim function to remove both types of spaces and means before and after line spaces.

SELECT LTRIM (RTRIM (REVERSE ("NEXT LEVEL")))