I can't update values in a column with their trimmed version. Why?

Question

I have a Microsoft Access database. In the database, there is a table, say 'MyTable'. In MyTable, there is a column, say 'MyColumn'. Its type is Text. Some data in this column have several space characters at their end, so I want to trim them. I ran the command below

UPDATE MyTable SET MyColumn = trim(MyColumn)

The command was executed successfully, but I see no change. The data with space suffix are still there. Why?

Many thanks in advance,

Haris

score 0 · Answer 1 · answered Jul 29 '10 at 10:40

0

One option is that you have a fixed-length field (which can be created programmatically). Another is that your spaces aren't true spaces but other characters (NBSP for instance) which just 'look' like spaces.

answered Jul 29 '10 at 10:40

Tobiasopdenbrouw

13,811
1
22
27

How to remove these space-like characters then? – Haris Jul 29 '10 at 11:00
Space like characters are often Chr(255) you can check for this with Mid, Left & Right functions. You can then use replace, if this is the case: replace(s,chr(255),"") – Fionnuala Jul 29 '10 at 11:18
Yes. 160 is another one, I think. – Tobiasopdenbrouw Jul 29 '10 at 11:26
Also see http://stackoverflow.com/questions/52315/t-sql-trim-nbsp-and-other-non-alphanumeric-characters – Tobiasopdenbrouw Jul 29 '10 at 11:38
You can find out the character that is at the end of the field with Asc(Right(MyColumn, 1)). – David-W-Fenton Jul 30 '10 at 21:02

I can't update values in a column with their trimmed version. Why?

1 Answers1