Remove Trailing Spaces and Update in Columns in SQL Server
SqlSql Server-2008TrailingSql Problem Overview
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"
Sql Solutions
Solution 1 - Sql
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))
Solution 2 - Sql
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))
Solution 3 - Sql
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 <= @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
Solution 4 - Sql
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))
Update : 2022
I had answered this question 7 years ago(in 2015). At that time there was not a direct function for trimming in SQL server.
But from SQL Server 2017, they introduced the Trim() function.
So anyone who uses SQL Server 2017 or a later version, can easily do the same thing as below.
update tablename
set ColumnName= TRIM(ColumnName)
However, if you use an older version, you will still have to use the way which I've mentioned 7 years ago.
Solution 5 - Sql
update MyTable set CompanyName = rtrim(CompanyName)
Solution 6 - Sql
Use the TRIM SQL function.
If you are using SQL Server try :
SELECT LTRIM(RTRIM(YourColumn)) FROM YourTable
Solution 7 - Sql
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'
Solution 8 - Sql
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 ;)
Solution 9 - Sql
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;
Solution 10 - Sql
SELECT TRIM(ColumnName) FROM dual;
Solution 11 - Sql
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)
In other versions you have to use set CompanyName = LTRIM(RTRIM(CompanyName))
Solution 12 - Sql
Example:
SELECT TRIM(' Sample ');
Result: 'Sample'
UPDATE TableName SET ColumnName = TRIM(ColumnName)
Solution 13 - Sql
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),'')