Remove Trailing Spaces and Update in Columns in SQL Server

Question

I have trailing spaces in a column in a SQL Server table called Company Name   All data in this column has trailing spaces   I want to remove all those  and I want to have the data without any trailing spaces   The company name is like  Amit Tech Corp            I want the company name to be  Amit Tech Corp

User · Answer

Use the TRIM SQL function   If you are using SQL Server try     SELECT LTRIM RTRIM YourColumn   FROM YourTable

User · Answer

SQL Server does not support for Trim   function    But you can use LTRIM   to remove leading spaces and RTRIM   to remove trailing spaces   can use it as LTRIM RTRIM ColumnName   to remove both   update tablename set ColumnName  LTRIM RTRIM ColumnName

User · Answer

Try  SELECT LTRIM RTRIM  Amit Tech Corp      LTRIM - removes any leading spaces from left side of string  RTRIM - removes any spaces from right  Ex    update table set CompanyName   LTRIM RTRIM CompanyName

User · Answer

Well here is a nice script to TRIM all varchar columns on a table dynamically   --Just change table name declare  MyTable varchar 100  set  MyTable    MyTable   --temp table to get column names and a row id select column name  ROW NUMBER   OVER ORDER BY column name  as id into  tempcols from INFORMATION SCHEMA COLUMNS  WHERE   DATA TYPE IN   varchar    nvarchar   and TABLE NAME    MyTable  declare  tri int select  tri   count    from  tempcols declare  i int select  i   0 declare  trimmer nvarchar max  declare  comma varchar 1  set  comma         --Build Update query select  trimmer    UPDATE  dbo        MyTable      SET    WHILE  i  lt    tri  BEGIN      IF   i    tri          BEGIN         set  comma              END     SELECT   trimmer    trimmer   CHAR 10         COLUMN NAME        LTRIM RTRIM      COLUMN NAME          comma     FROM     tempcols     where id    i      select  i    i 1 END  --execute the entire query EXEC sp executesql  trimmer  drop table  tempcols

User · Answer

I had the same problem after extracting data from excel file using ETL and finaly i found solution there    https   www codeproject com Tips 330787 LTRIM-RTRIM-doesn-t-always-work  hope it helps

User · Answer

To remove Enter   Update  table name  set  column name  Replace REPLACE  column name  CHAR 13      CHAR 10        To remove Tab   Update  table name  set  column name  REPLACE  column name  CHAR 9

User · Answer

Well  it depends on which version of SQL Server you are using   In SQL Server 2008 r2  2012 And 2014 you can simply use TRIM CompanyName   SQL Server TRIM Function  In other versions you have to use set CompanyName   LTRIM RTRIM CompanyName

User · Answer

SELECT TRIM ColumnName  FROM dual

User · Answer

If we also want to handle white spaces and unwanted tabs-  Check and Try the below script  Unit Tested -  --Declaring DECLARE  Tbl TABLE col 1 VARCHAR 100     --Test Samples INSERT INTO  Tbl  col 1  VALUES     EY     y             Salem         EY     P    ort       Chennai            EY     Old           Park            EY            EY             null    d                                f     SELECT col 1 AS INPUT      LTRIM RTRIM      REPLACE      REPLACE      REPLACE      REPLACE      REPLACE          REPLACE          REPLACE          REPLACE          REPLACE          REPLACE          REPLACE col 1 CHAR 10                CHAR 11                CHAR 12                CHAR 13                CHAR 14                CHAR 160                CHAR 13  CHAR 10            CHAR 9                CHAR 17  CHAR 18        CHAR 18  CHAR 17           CHAR 17  CHAR 18              AS  OUTPUT  FROM  Tbl

User · Answer

To just trim trailing spaces you should use  UPDATE     TableName SET     ColumnName   RTRIM ColumnName    However  if you want to trim all leading and trailing spaces then use this  UPDATE     TableName SET     ColumnName   LTRIM RTRIM ColumnName

User · Answer

If you are using SQL Server  starting with vNext   or  Azure SQL Database then you can use the below query   SELECT TRIM ColumnName  from TableName    For other SQL SERVER Database you can use the below query   SELECT LTRIM RTRIM ColumnName   from TableName   LTRIM - Removes spaces from the left  example  select LTRIM      test       as trim    test       RTRIM - Removes spaces from the right  example  select RTRIM      test       as trim        test

User · Answer

Example   SELECT TRIM     Sample         Result   Sample   UPDATE TableName SET ColumnName   TRIM ColumnName

User · Answer

update MyTable set CompanyName   rtrim CompanyName

[sql] Remove Trailing Spaces and Update in Columns in SQL Server

Examples related to sql

Examples related to sql-server-2008

Examples related to trailing