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"

rs. · Accepted Answer · 2018-06-06T18:42:00.573

357

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))

edited Jun 06 '18 at 18:42

answered Feb 08 '13 at 22:00

rs.

26,707
12
68
90

21

It should be noted that [TRIM](https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql) is now a supported method in SQL Server 2017+. – DJ Sipe Feb 15 '18 at 21:44
3

I am using SQL Management Studio v17.8.1 and while I get Intellisense for the TRIM function, when I execute it, it says it is not valid. I had to use the code above. Weird. – DesertFoxAZ Nov 02 '18 at 17:05
5

@DesertFoxAZ SQL Management Studio version is not version of SQL Server – Jonatan Dragon Jan 25 '19 at 13:56

score 38 · Answer 2 · answered Feb 08 '13 at 22:01

38

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))

answered Feb 08 '13 at 22:01

Robin Day

100,552
23
116
167

Thilina Sandunsiri · Answer 3 · 2022-01-22T09:38:48.990

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.

score 16 · Answer 4 · answered Mar 07 '14 at 22:06

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

score 7 · Answer 5 · answered Feb 08 '13 at 22:02

7

update MyTable set CompanyName = rtrim(CompanyName)

answered Feb 08 '13 at 22:02

Christoffer Lette

14,346
7
50
58

score 3 · Answer 6 · answered Feb 08 '13 at 22:02

3

Use the TRIM SQL function.

If you are using SQL Server try :

SELECT LTRIM(RTRIM(YourColumn)) FROM YourTable

answered Feb 08 '13 at 22:02

Simon

1,605
13
22

Sukanta Saha · Answer 7 · 2017-03-20T04:32:27.210

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'

score 2 · Answer 8 · answered May 13 '17 at 10:20

2

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 ;)

answered May 13 '17 at 10:20

Salim Lyoussi

236
3
9

score 1 · Answer 9 · answered Jan 18 '20 at 10:34

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;

score 0 · Answer 10 · edited Mar 26 '18 at 02:47

0

SELECT TRIM(ColumnName) FROM dual;

edited Mar 26 '18 at 02:47

Pang

9,564
146
81
122

answered Dec 07 '14 at 04:37

shevin

11

1

This is an Oracle query, OP is using Microsoft SQL Server – Wouter Sep 02 '16 at 14:35

score 0 · Answer 11 · answered Apr 16 '15 at 19:48

0

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))

answered Apr 16 '15 at 19:48

Daniel Urdaneta

37
3

3

Trim is not available by default, it's a DAX feature: https://msdn.microsoft.com/en-us/library/gg413422.aspx – Wouter Sep 02 '16 at 14:37
TRIM() available from SQL2017 https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql (and has option to remove characters other than SPACE) – Kristen Jan 06 '21 at 12:19

score 0 · Answer 12 · edited Oct 29 '15 at 13:16

0

Example:

SELECT TRIM('   Sample   ');

Result: 'Sample'

UPDATE TableName SET ColumnName = TRIM(ColumnName)

edited Oct 29 '15 at 13:16

Anders

8,307
9
56
88

answered Oct 29 '15 at 11:12

pritam

11

3

SQL Server does not supported for Trim() function. But you can use LTRIM() and RTRIM(). If you want to use both together you can use LTRIM(RTRIM(ColumnName)) – Thilina Sandunsiri Dec 14 '15 at 05:32
This is an Oracle query, OP is using Microsoft SQL Server – Wouter Sep 02 '16 at 14:35

score 0 · Answer 13 · edited Oct 01 '18 at 04:51

0

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),'')

edited Oct 01 '18 at 04:51

Stephen Rauch

47,830
31
106
135

answered Oct 01 '18 at 04:31

Pieter

1
1

score 0 · Answer 14 · answered Jun 16 '23 at 14:11

0

None of these will work if your datatype is Varchar. Make sure to change your datatype to Nvarchar.

answered Jun 16 '23 at 14:11

toly P

44
7

Marek Pio · Answer 15 · 2023-07-06T10:42:52.840

TrimShort.sql

/* Summary: Trims the data of selected columns in a selected table
* Works for: SQL Server Management Studio v18.10 
* 
* How to use:
* ctrl + F, 
* click V on the left side of textbox
* copy a field's name from list below > paste in Search
* write your field's name > Replace in Search
* click Replace All
* repeat for all fields
*
* Fields to replace:
* MyDatabase - set to your database name
* MyTable - set to your table name
* Column1 - set to your table's column name
* Column2 - set to your table's column name
*/

USE [MyDatabase]
GO
UPDATE [dbo].[MyTable] SET
    [Column1] = TRIM([Column1])
    ,[Column2] = TRIM([Column2])
GO

TrimLong.sql - I wrote this first. It works like above version.

/* Summary:
* Trim the data of selected columns in a selected table
*
* Works for: SQL Server Management Studio v18.10 
* 
* How to use:
* ctrl + F, 
* click V on the left side of textbox
* copy a field's name from list below > paste in Search
* write your field's name > Replace in Search
* click Replace All
* repeat for all fields
*
* Fields to replace:
* MyDatabase - set to your database name
* MyTable - set to your table name
* MyColumnId - set to your table's column ID name
* Column1 - set to your table's column name
* Column2 - set to your table's column name
*/

USE [MyDatabase]
GO

--Number of rows in the table
DECLARE @RowCnt INT;
SET @RowCnt = (SELECT COUNT(*) FROM [dbo].[MyTable])

--Row ID for a loop
DECLARE @RowId INT;
SET @RowId = 1

--Print number of rows to Update
PRINT 'Number of rows:' + convert(varchar(4), @RowCnt)

--Loop for each row
WHILE @RowId <= @RowCnt
BEGIN
    --Print Row ID
    PRINT 'RowId:' + convert(varchar(4), @RowId)

    --Trim a row data for selected Columns
    UPDATE [dbo].[MyTable] SET
        [Column1] = (SELECT TRIM([Column1]) from [dbo].[MyTable] WHERE MyColumnId = @RowId)
        ,[Column2] = (SELECT TRIM([Column2]) from [dbo].[MyTable] WHERE MyColumnId = @RowId)
        WHERE MyColumnId = @RowId

    --Increase Row ID
    SET @RowId = @RowId + 1
END
GO

Example of use

When you were cleaver enough to make char(15) fields but then it turned out that you were reading a value with a whitespace tail, so you will change it to nvarchar(15).

Remove Trailing Spaces and Update in Columns in SQL Server

15 Answers15

Example of use

Linked