Trim spaces in string - LTRIM RTRIM not working

Question

I tried this code -

UPDATE Table
SET Name = RTRIM(LTRIM(Name))

Data type of Name is varchar(25)

None of the leading and trailing spaces get removed. When I copy-paste one such Name, i get this -

"big dash" "space symbol" ABC001

Why is this happening and how do trim the spaces ?

EDIT -

The question has already been answered. I found one more table with this problem. I get "- value" when i copy a column of a row. When I press the enter key at end of this copy-pasted value, i see more dashes. See image below -

Answer 1

The space character is represented in two different way one with ASCII code number 32 and another with ASCII code number 160. TRIM function will not trim the non-breaking space char.

DECLARE @nonbreakingspaceString VARCHAR(10) = 'String '
DECLARE @normalspaceString VARCHAR(10) = 'String '

Non-breaking space Char with ASCII code 160, returns 160 in below select query.

SELECT CHAR(160)
SELECT ascii(' ') -- Replace the non-breaking space Char with the above result, as non-breaking char is not applied here.

Space Char with ASCII 32, returns 32 in below select query.

SELECT CHAR(32)
SELECT ascii(' ')

String with non-breaking space ASCII code 160, returns length 7 as this does not TRIM the string

SELECT LEN(TRIM(@nonbreakingspaceString))

String with space ASCII code 32, TRIMS the string and returns length 6.

SELECT LEN(TRIM(@normalspaceString))

So when we encounter a situation were string is not getting trimmed, we can use REPLACE as below to replace the non-breaking space char. Below code returns length of string as 6.

SELECT LEN(REPLACE(@nonbreakingspaceString, CHAR(160), ''))

Answer 2

I use this command while updating all rows' value. It'll really work out. For your example:

UPDATE Table SET Name = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(Name, CHAR(10), CHAR(32)), CHAR(13), CHAR(32)), CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))

Answer 3

Use this to identify the offending character:

select ascii(substring(' Your string with leading invisible character',1,1));
-- returns something like 160

Use this to replace the offending character

replace(' Your string with leading invisible character', char(160),'')

Answer 4

You can use the HEX method above, or you can also use the ASCII() function to determine the ASCII code of the character in question...

SELECT ASCII(SUBSTRING('  character string', 1, 1)) 
SELECT ASCII(SUBSTRING('  character string', 2, 1))

The select only returns 1 value for the character you specify. But it's helpful for determining which ASCII CHAR() value(s) you need to replace.

-Eric Isaacs

Answer 5

There are cases that the LTRIM RTRIM not doing what you want, to me, it happened because of the tab key when tab key inserted to a database we cant see it in our eyes in this cases trim function doesn't work.

Try this code

UPDATE <TablaName> SET NAME = CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(value, CHAR(9), ''), CHAR(13), ''), CHAR(10), ''))) AS VARCHAR(50))

Answer 6

Kindly use below query it will remove space new line etc..

select LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(Name, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))

Answer 7

It is a frequent occurrence that we must remove leading and trailing whitespaces from a string before additional processing or sending it to another layer in an application. We can’t always control how the data is entered. The data might come from another system, a data conversion, an old application, EDI, Excel, or from an application which had poor quality control. In some of those cases, a whitespace might not be entered or saved in the system as character 32 which is a whitespace entered in a keyboard. If that happens, SQL built in functions for trimming whitespaces do not work so it becomes necessary to replace the “other” whitespace characters with character 32. Then LTRIM and RTRIM will work as expected.

**Select [udfTrim](ColumnName) from Table**

**CREATE FUNCTION [dbo].[udfTrim] 
(
    @StringToClean as varchar(8000)
)**
RETURNS varchar(8000)
AS
BEGIN   
    --Replace all non printing whitespace characers with Characer 32 whitespace
    --NULL
    Set @StringToClean = Replace(@StringToClean,CHAR(0),CHAR(32));
    --Horizontal Tab
    Set @StringToClean = Replace(@StringToClean,CHAR(9),CHAR(32));
    --Line Feed
    Set @StringToClean = Replace(@StringToClean,CHAR(10),CHAR(32));
    --Vertical Tab
    Set @StringToClean = Replace(@StringToClean,CHAR(11),CHAR(32));
    --Form Feed
    Set @StringToClean = Replace(@StringToClean,CHAR(12),CHAR(32));
    --Carriage Return
    Set @StringToClean = Replace(@StringToClean,CHAR(13),CHAR(32));
    --Column Break
    Set @StringToClean = Replace(@StringToClean,CHAR(14),CHAR(32));
    --Non-breaking space
    Set @StringToClean = Replace(@StringToClean,CHAR(160),CHAR(32));

    Set @StringToClean = LTRIM(RTRIM(@StringToClean));
    Return @StringToClean
END

Answer 8

If your string has some non-unicode chars, then those need to be removed first. The functions for that are given later, taken from this link - http://iso30-sql.blogspot.com/2010/10/remove-non-printable-unicode-characters.html

First, check if there are any weird hex chars using -

select convert(varbinary, Name) from table

Then, use the code given in the link above. Note that in the usage of functions, square brackets are to be removed, otherwise the code won't work. Eg. [@DatabaseName = 'MyDatabaseName',] [@SchemaName = 'MySchemaName',]

After this, your strings might have some spaces which can be removed using -

UPDATE Table
SET Name = RTRIM(LTRIM(Name))

Also NOTE that the scripts given in the above link/below will not work on the following table -

CREATE TABLE [dbo].[Junk](
    [JunkHex] nvarchar(50) NULL
) ON [PRIMARY]
GO

GO
INSERT [dbo].[Junk] ([JunkHex]) VALUES (N'Stringğ ')
INSERT [dbo].[Junk] ([JunkHex]) VALUES (N'withħ')
INSERT [dbo].[Junk] ([JunkHex]) VALUES (N'įņvalidđ')
INSERT [dbo].[Junk] ([JunkHex]) VALUES (N'charactersŝ')

---

This is the content of the link I have given above -

Remove non-printable / Unicode characters in SQL Server 2005 A few months ago, I was upgrading some report templates from the older version of Excel (.xls) to Excel 2007 (.xlsx). I ran into numerous problems almost immediately when I attempted to generate the upgraded reports because the incoming data was riddled with charaters that don't play nicely with XML. The data is used for a variety of reporting purposes, so I decided to tackle the problem on the back-end by removing all but the printable ascii characters.

I started by writing a simple user function for individual strings, but I got to thinking that I may want to automate some of these cleanup tasks and ended up putting something together that allows for a bit more the flexibility. The following creates the basic string user function, along with two procedures to perform the cleanup at the column and table level:

Note - Each of the scripts below uses all the ones above it. So, execute all scripts in order to get all functionality.

Function: fn_npclean_string

use [master]
go
set ansi_nulls on
go
set quoted_identifier on
go
CREATE function [dbo].[fn_npclean_string] (
 @strIn as varchar(1000)
)
returns varchar(1000)
as
begin
 declare @iPtr as int
 set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
 while @iPtr > 0 begin
  set @strIn = replace(@strIn COLLATE LATIN1_GENERAL_BIN, substring(@strIn, @iPtr, 1), '')
  set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
 end
 return @strIn
end

Procedure: sp_npclean_col

use [master]
go
set ansi_nulls on
go
set quoted_identifier on
go
CREATE procedure [dbo].[sp_npclean_col]
 @DatabaseName varchar(75) = null,
 @SchemaName varchar(75) = null,
 @TableName varchar(75),
 @ColumnName varchar(75)
as
begin
 Declare @FullTableName varchar(100)
 declare @UpdateSQL nvarchar(1000)
 if @DatabaseName is null begin
  set @DatabaseName = db_name()
 end
 if @SchemaName is null begin
  set @SchemaName = schema_name()
 end
 set @FullTableName = '[' + @DatabaseName + '].[' + @SchemaName + '].[' + @TableName + ']'
 set @UpdateSQL = 'update ' + @FullTableName + ' set [' + @ColumnName + '] = dbo.fn_npclean_string([' + @ColumnName + ']) where [' + @ColumnName + '] like ''%[^ -~0-9A-Z]%'''
 exec sp_ExecuteSQL @UpdateSQL
end

Procedure: sp_npclean_table

use [master]
go
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[sp_npclean_table] 
 @TargetDatabase varchar(75) = null,
 @TargetSchema varchar(75) = null,
 @TargetTable varchar(75)
as
begin
 declare @getColSQL nvarchar(750)
 declare @textCol CURSOR
 declare @curCol varchar(75)
 if @TargetDatabase is null begin
  set @TargetDatabase = db_name()
 end
 if @TargetSchema is null begin
  set @TargetSchema = schema_name()
 end
 set @getColSQL =
  'select sc.name
  from ' + @TargetDatabase + '.sys.columns sc
  join ' + @TargetDatabase + '.sys.types st
  on sc.system_type_id = st.system_type_id
  join ' + @TargetDatabase + '.sys.objects so
  on sc.object_id = so.object_id
  join ' + @TargetDatabase + '.sys.schemas ss
  on so.schema_id = ss.schema_id
  where
  so.type = ''U''
  and st.name in (''text'',''ntext'',''varchar'',''char'',''nvarchar'',''nchar'')
  and sc.is_rowguidcol = 0
  and sc.is_identity = 0
  and sc.is_computed = 0
  and so.name = ''' + @TargetTable + '''
  and ss.name = ''' + @TargetSchema + ''''
 set @getColSQL = 'set @inCursor = cursor for ' + @getColSQL + ' open @incursor'
 execute sp_executesql @getColSQL,N'@inCursor cursor out',@inCursor=@textCol OUT
 fetch next from @textCol into @curCol
 while @@fetch_status = 0
 begin
  exec sp_npclean_col @DatabaseName = @TargetDatabase, @SchemaName = @TargetSchema, @TableName = @TargetTable, @ColumnName = @curCol
  fetch next from @textCol into @curCol
 end
 Close @textCol
 DeAllocate @textCol
end

Using these, invalid characters can be removed in the following ways:

By String:

select master.dbo.fn_npclean_string('Stringğ withħ įņvalidđ charactersŝ')

By table column:

exec master.dbo.sp_npclean_col [@DatabaseName = 'MyDatabaseName',] [@SchemaName = 'MySchemaName',] @TableName = 'MyTableName',  @ColumnName = 'MyColumnName'

By table:

exec master.dbo.sp_npclean_table [@TargetDatabase = 'MyDatabaseName',] [@TargetSchema = 'MySchemaName',] @TargetTable = 'MyTableName'

Answer 9

You could do something brute force, such as removing the first character "manually" if it is not alphanumeric:

update table
    set name = rtrim(ltrim(case when name not like '[a-zA-Z0-9]%'
                                then stuff(name, 1, 1, '')
                                else name
                           end)
                    );

You could also search and replace that particular character:

update table
    set name = rtrim(ltrim(replace(name, "big dash", '')));

Answer 10

I suspect, some non readable(Non-ascii characters) inside the name column, that might not get removed as part of TRIM calls.

select convert(varbinary, Name) from table

Reading the HEX output from above query should reveal the same.

Kindly read this to find how to write functions to remove such characters.