Remove trailing empty space in a field content

Question

I am using SQL server MSDE 2000. I have a field called notes of type nvarchar(65).

The content is 'Something ' with an extra space after the content (quotes for clarity) in all the records. I used the following command.

UPDATE TABLE1 
   SET notes = RTRIM(LTRIM(notes))

But it does not work. Is there any alternate way to do it?

Fleabag

what do you mean by "does not work"? It should .... – Boccie

No sure. The query gets executed properly and I get the message '(12539 row(s) affected)'. But the field still has the value with a trailing space. – Fleabag

you looking at the right server instance, table ?? I done that before... – Boccie

RTRIM should work fine. Are you sure the character afterwards is actually a space? You might want to check the collation you're using and the byte value. – Enjambement

Answer 1

Are you sure the query isn't working? Try:

SELECT TOP 100 '~'+ t.notes +'~'
  FROM TABLE1 t

TOP 100 will limit the results to the first 100 rows, enough to get an idea if there's really a space in the output. If there is, and RTRIM/LTRIM is not removing it - then you aren't dealing with a whitespace character. In that case, try:

UPDATE TABLE1
  SET notes = REPLACE(notes, 
                      SUBSTRING(notes, PATINDEX('%[^a-zA-Z0-9 '''''']%', notes), 1), 
                      '')
WHERE PATINDEX('%[^a-zA-Z0-9 '''''']%', notes) <> 0

Provenience

This one works. What is the difference between this and the one I have used? – Fleabag

@Bharanidharan: It removes non printable ASCII characters, which LTRIM/RTRIM does not. – Provenience

Fine. What is a non-printable ACSII character? Is it different from a blank space? – Fleabag

The first 32 values are non-printing control characters, such as Carriage Return (decimal value 13) and Line Feed (decimal value 10): csgnetwork.com/asciiset.html – Provenience

Answer 2

... OR you could literally just copy/paste the blank ' ' (space) at the end of a field as a result of your query into your replace statement and update everything from there.

update TABLE1
set notes = replace(notes, ' ', '')

Fulgurating

Answer 3

And just in case you need to TRIM all spaces in all columns, you can use this script to do it 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

Mullet