OraFAQ Forum: SQL & PL/SQL » update : Eliminate blancs

Home » SQL & PL/SQL » SQL & PL/SQL » update : Eliminate blancs

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

update : Eliminate blancs [message #8971]

Wed, 08 October 2003 10:42

Abiad
Messages: 9
Registered: October 2003

Junior Member

Hi,
Say I have EMP table
EMP (empno, job)

empno job
1 'president'
2 'clerk'
3 'clerk '
4 'clerk'
5 'manager '
6 'manager'

I want to eliminate the blancs. We dont know the string to replace, but we know that is the string whith 1 blanc is always in the end.

SQL> update table emp
set job=replace(job, 'clerk ', 'clerk') ;

idem for manager

Thanks for help
YA

Report message to a moderator

Re: update : Eliminate blancs [message #8973 is a reply to message #8971]

Wed, 08 October 2003 12:08

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

update emp
   set job = replace(job, ' ')
 where instr(job, ' ') > 0;

Report message to a moderator

Re: update : Eliminate blancs [message #8974 is a reply to message #8971]

Wed, 08 October 2003 12:11

sverch
Messages: 582
Registered: December 2000

Senior Member

UPDATE emp SET job=TRIM(job);

or if you version of Oracle does not have TRIM

UPDATE emp SET job=LTRIM(RTRIM(job));

Report message to a moderator

Re: update : Eliminate blancs [message #8978 is a reply to message #8973]

Wed, 08 October 2003 13:33

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

As sverch pointed out, if the blank space is always at the end, you can just use RTRIM.

Report message to a moderator

Re: update : Eliminate blancs [message #9006 is a reply to message #8974]

Fri, 10 October 2003 11:52

Yhab
Messages: 7
Registered: October 2003

Junior Member

Both work.
Thank you

YA

Report message to a moderator

Re: update : Eliminate blancs [message #9008 is a reply to message #8974]

Fri, 10 October 2003 11:52

Yhab
Messages: 7
Registered: October 2003

Junior Member

Both work.
Thank you

YA

Report message to a moderator

Re: update : Eliminate blancs [message #9013 is a reply to message #8973]

Fri, 10 October 2003 13:22

Yhab
Messages: 7
Registered: October 2003

Junior Member

Thank you Todd,
Euhh blancs between words was eliminated too (all blacncs in a string was eliminated)

I can use your QUERY for other usage

For my need, TRIM work well
Thank you
YA

Report message to a moderator

Previous Topic:	Query problem (using wildcarding)
Next Topic:	count

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 20:10:05 CDT 2024