Remove Trailing Spaces and Update in Columns in SQL Server

Remove Trailing Spaces and Update in Columns in SQL Server

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

Removing trailing spaces and whitespaces from SQL Server column

Check the below;

1. Find any special characters like char(10), char(13) etc in the field value.
2. Check the status of ANSI_PADDING ON. Refer this MSDN article.

How to remove trailing spaces like characters in SQL Server

It was likely either char(9), char(10), or char(13) (tab,lf,cr; respectively).

You can read up on them here: https://learn.microsoft.com/en-us/sql/t-sql/functions/char-transact-sql?view=sql-server-2017

You can remove them using REPLACE().

Such as:

DECLARE @VARIABLE VARCHAR(10)

SET @VARIABLE='RM004'+CHAR(10)+CHAR(10)

SELECT @VARIABLE, LEN(@VARIABLE)

SET @VARIABLE = REPLACE(@VARIABLE, CHAR(9),'')
SET @VARIABLE = REPLACE(@VARIABLE, CHAR(10),'')
SET @VARIABLE = REPLACE(@VARIABLE, CHAR(13),'')

SELECT @VARIABLE, LEN(@VARIABLE)

How to remove white spaces from columns in SQL Server 2000?

you can use REPLACE/RTRIM/LTRIM

select RTRIM(LTRIM(column_name)) from table_name

or

select  replace(column_name, ' ', '') from table_name

Remove multiple Trailing Spaces and Update in Columns in Oracle

Here is a hack for your problem. You will have to work on the procedure if you will use it more than once. In particular I do not handle errors, and I look only for VARCHAR2 columns.

SETUP:

create table tbl ( id number, name varchar2(10), notes varchar2(30) );

insert into tbl values ( 1, 'Joe  ', 'No notes' );
insert into tbl values ( 2, 'Ann'  , 'Spaces  ' );
insert into tbl values ( 3, 'Ben'  , null       );
commit;

select id, '*|'||name||'|*' as name, '*|'||notes||'|*' as notes
from   tbl
;

ID NAME           NOTES                            
-- -------------- ----------------------------------
 1 *|Joe  |*      *|No notes|*                      
 2 *|Ann|*        *|Spaces  |*                      
 3 *|Ben|*        *||*              

Notice the name in the first row, and the notes in the second - they have trailing spaces. I added leading *| and trailing |* to show where the trailing spaces are.

The proper way to update this table is

update tbl
  set name = rtrim(name), notes = rtrim(notes)
  where name like '% ' or notes like '% '
;

- notice the WHERE clause, to make sure we don't update rows that don't have any values with trailing spaces. (Updating rows to themselves may seem harmless, but it adds significant overhead, with all the undo and redo they generate.)

So, then, here is PL/SQL code that generates this UPDATE statement dynamically:

create or replace procedure trim_trailing_spaces (
  tbl_name in varchar2,
  own      in varchar2 default null
)
as
  sql_text varchar2(4000)
    := 'update ' || nvl(own, user) || '.' || tbl_name;
  set_text varchar2(1000)
    := chr(10) || '  set   ';
  where_text varchar2(1000)
    := chr(10) || '  where ';
begin
  for r in ( select column_name as col
             from   all_tab_columns
             where  owner       = nvl(upper(own), user)
               and  table_name  = upper(tbl_name)
               and  data_type = 'VARCHAR2'
           )
  loop
    set_text := set_text || r.col || ' = rtrim( ' || r.col || '), ';
    where_text := where_text || r.col || ' like ''% '' or ';
  end loop;

  sql_text := sql_text || rtrim(set_text, ', ') || rtrim(where_text, 'or ');
  execute immediate sql_text;
  commit;
end trim_trailing_spaces;
/

Compile it and then execute it. I pass in the table name 'tbl' (the table we created earlier) and I don't give the optional second argument (for a schema name different from my own), so the updated table will be the one in my schema. Then I execute the SELECT statement from above to see the changes.

exec trim_trailing_spaces('tbl')

select id, '*|'||name||'|*' as name, '*|'||notes||'|*' as notes
from   tbl
;

ID NAME           NOTES                            
-- -------------- ----------------------------------
 1 *|Joe|*        *|No notes|*                      
 2 *|Ann|*        *|Spaces|*                      
 3 *|Ben|*        *||*   

So, it seems like it worked.

Remove all spaces from a string in SQL Server

Simply replace it;

SELECT REPLACE(fld_or_variable, ' ', '')

Edit:
Just to clarify; its a global replace, there is no need to trim() or worry about multiple spaces for either char or varchar:

create table #t (
    c char(8),
    v varchar(8))

insert #t (c, v) values 
    ('a a'    , 'a a'    ),
    ('a a  '  , 'a a  '  ),
    ('  a a'  , '  a a'  ),
    ('  a a  ', '  a a  ')

select
    '"' + c + '"' [IN], '"' + replace(c, ' ', '') + '"' [OUT]
from #t  
union all select
    '"' + v + '"', '"' + replace(v, ' ', '') + '"'
from #t 

Result

IN             OUT
===================
"a a     "     "aa"
"a a     "     "aa"
"  a a   "     "aa"
"  a a   "     "aa"
"a a"          "aa"
"a a  "        "aa"
"  a a"        "aa"
"  a a  "      "aa"

---

Related Topics