CODEWITHSUNDEEP
sqloracle-database
kralco626
kralco626

Reputation: 8624

oracle trim data in a table col

I know that Oracle has the TRIM function and I would like to use it to trim the whitespace out of a field in one of my tables.

As such:

update THIRD_PARTY_ADRS_INFO_TEMP
set HOUSE_NO = TRIM(HOUSE_NO);

just hangs when i try to run it in SQL Developer.

I have also tried TRIM(' ' from HOUSE_NO) and REPLACE(HOUSE_NO,' ','') all with the same effect

This seems like it should be really simple...

ideas?

Upvotes: 1

Views: 5418

Answers (3)

Michael Broughton
Michael Broughton

Reputation: 4055

Glad you solved the row lock. As to removing all sorts of blank space, you can also look into REGEXP_REPLACE if on a recent Oracle version. It is a bit slower than trim, but if you have multiple possible non-printing characters to deal with it might be worth a look.

e.g.

select regexp_replace(x,'[[:space:]|[:blank:]|[:cntrl:]]*$','')
   from (select 'ad f cde  '||chr(10)||chr(13)||chr(8)||' ' as x from dual);

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132570

If it "hangs" then this suggests that your session is blocked by another session. You are trying to update every row in the table; if another session has locked a row in the same table and not yet committed your session will have to wait.

Upvotes: 3

Greg Reynolds
Greg Reynolds

Reputation: 10186

How many rows in your table? You could try just doing a select rather than an update (with rownum < 10 for example) just to satisfy yourself that it's working.

Upvotes: 1

Related Questions