Reputation: 89

Oracle Update Column LTRIM and RTRIM

I am wish to update my database data in SQL Developer using

UPDATE TABLE_NAME SET COLUMN_NAME = LTRIM(RTRIM(COLUMN_NAME))

But it does not take any effect even the msg of "rows updated" is displayed. The leading and trailing white spaces still exist behind every strings.

Any ideas?

Upvotes: 2

Answers (5)

Reputation: 31

I had the same problem on two different tables. One the first table trim was ok, on the second no effect, the spaces were still there!

The difference was that in the first table I was using varchar2(30) and in the second table (where trim didn't work) I had char(30)

After modifying the second table from char(30) to varchar2(30) trim worked as expected.

Upvotes: 3

Reputation: 470

Do you commit after update?

update tableName set col1 = trim(col1);
commit;

Upvotes: 11

Reputation: 52376

You get a message saying that n rows are updated because there are n rows in your table and you are applying the update to all of them.

To limit the update to only those rows where the update will have an effect, use:

UPDATE TABLE_NAME
SET    COLUMN_NAME = LTRIM(RTRIM(COLUMN_NAME))
WHERE  COLUMN_NAME != LTRIM(RTRIM(COLUMN_NAME));

Upvotes: 0

Reputation: 23

Have you tried REGEXP_REPLACE(your column name, '\s*', '') ?

ex: UPDATE TABLE_NAME SET COLUMN_NAME = REGEXP_REPLACE(COLUMN_NAME, '\s*', '')

Upvotes: 1

Reputation: 15387

Try this

UPDATE TABLE_NAME SET COLUMN_NAME = TRIM(COLUMN_NAME);

Upvotes: 2