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

Question

I m in the process of trying to clear out leading and trailing spaces from an NVARCHAR MAX  column that is filled with prices  using NVARCHAR due to data importing from multiple operating systems with odd characters      At this point I have a t-sql command that can remove the leading trailing spaces from static prices   However  when it comes to leveraging this same command to remove all prices  I m stumped   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 s what I ve tried to remove all the trailing spaces   UPDATE  tablename  set  columnname  LIKE LTRIM RTRIM         WHERE cost LIKE           I ve also tried different varations of the   for random characters but at this point I m spinning my wheels   What I m hoping to achieve is to run one simple command that takes off all the leading and trailing spaces in each cell of this column without modifying any of the actual column data

User · Answer

I understand this question is for sql server 2012  but if the same scenario for SQL Server 2017 or SQL Azure you can use Trim directly as below  UPDATE  tablename     SET  columnname    trim  columnname

User · Answer

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

User · Answer

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

User · Answer

SELECT RTRIM   Author    AS Name    Output will be without any trailing spaces   Name                           Author

User · Answer

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

User · Answer

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 spaces of string   SELECT LTRIM RTRIM REVERSE   NEXT LEVEL EMPLOYEE

[sql-server] How can I use LTRIM/RTRIM to search and replace leading/trailing spaces?

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2012