oracle trim data in a table col

Question

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?

Tony Andrews · Accepted Answer · 2011-03-10 14:10:36Z

3

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.

answered Mar 10, 2011 at 14:10

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kralco626 Over a year ago

I'm going to guess that this was the issue. I restarted SQL developer and it worked...

kralco626 Over a year ago

even though the trim didn't actually remove all the white space... I must have some tabs in there or something...

Gary Myers Over a year ago

I'd add a "WHERE HOUSE_NO != TRIM(HOUSE_NO)" clause to prevent wasting time on unnecessary updates

Jeffrey Kemp Over a year ago

You have to be careful, in SQL Developer (as in several other products) it is possible to run some DML in the "main session", then if you click on the "Run SQL" button it runs it in a new session - causing you to block against yourself. I've gotten into the habit of hitting the Rollback button before clicking on the "Run SQL" button.

Michael Broughton · Accepted Answer · 2011-03-10 16:32:46Z

2

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);

answered Mar 10, 2011 at 16:32

Michael Broughton

4,05516 silver badges12 bronze badges

1 Comment

Nick Perkins Over a year ago

+1 because this is the only answer which relates to "oracle trim". I got to this page because I searched on "oracle trim". The accepted answer helped the OP, but will probably not help anyone else who finds this page. This answer was most helpful to me.

Greg Reynolds · Accepted Answer · 2011-03-10 14:09:27Z

1

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.

answered Mar 10, 2011 at 14:09

Greg Reynolds

10.5k15 gold badges53 silver badges63 bronze badges

3 Comments

Erkan Haspulat Over a year ago

And if your table is huge, try creating an index on the column HOUSE_NO.

Greg Reynolds Over a year ago

Although he's updating every row, so an index may not help?

Gary Myers Over a year ago

Adding an index on HOUSE_NO would also mean that every entry in that index would need the change reflected in it.

Collectives™ on Stack Overflow

oracle trim data in a table col

3 Answers 3

4 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related