Home » SQL & PL/SQL » SQL & PL/SQL » Removing underscores
Removing underscores [message #254408] Thu, 26 July 2007 12:45 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello All,

I have my emp name data as :

EMPLPOYEE NAME
--------------------
SAM_WILLIAMS
NAGESH_KAPOOR
MIKE_CHAN


And now I want to replace the underscore symbol(_) with spaces

like:


EMPLPOYEE NAME
--------------------

SAM WILLIAMS
NAGESH KAPOOR
MIKE CHAN

Can somebody help me on this.

Regards,

Raj
Re: Removing underscores [message #254409 is a reply to message #254408] Thu, 26 July 2007 12:47 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Can somebody help me on this.

As opposed to you doing the work yourself by using either TRANSLATE or REPLACE functions?
Re: Removing underscores [message #254410 is a reply to message #254409] Thu, 26 July 2007 13:00 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member

Sir,

Here I want to update all the records at one go in my emp table who all have underscores(_) in between their names and replace with spaces as mentioned in my query.


so , plz lemme knw.


Regards,

Raj
Re: Removing underscores [message #254411 is a reply to message #254408] Thu, 26 July 2007 13:04 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Lookup the TRANSLATE or REPLACE functions.

Do something on your own.
Re: Removing underscores [message #254413 is a reply to message #254411] Thu, 26 July 2007 13:09 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
SQL> select table_name from test;

TABLE_NAME
------------------------------
USER$
USER_ASTATUS_MAP
USER_HISTORY$
SQL>  select replace(table_name,'_',' ') from test;

REPLACE(TABLE_NAME,'_','')
------------------------------
USER$
USER ASTATUS MAP
USER HISTORY$
USER PROFILE
USER PROFILE


SQL>


SQL> select table_name from test;

TABLE_NAME
------------------------------
USER$
USER_ASTATUS_MAP
USER_HISTORY$
USER_PROFILE
USER_PROFILE

SQL> select translate(table_name,'_',' ') from test;

TRANSLATE(TABLE_NAME,'_','')
------------------------------
USER$
USER ASTATUS MAP
USER HISTORY$
USER PROFILE
USER PROFILE

[Updated on: Thu, 26 July 2007 13:13]

Report message to a moderator

Re: Removing underscores [message #254417 is a reply to message #254413] Thu, 26 July 2007 13:25 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
thx a lot for that answer.
Re: Removing underscores [message #254448 is a reply to message #254410] Thu, 26 July 2007 15:06 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
rsreddy28 wrote on Thu, 26 July 2007 14:00

so , plz lemme knw.



I recognize one word in that abomination.
Re: Removing underscores [message #254722 is a reply to message #254413] Fri, 27 July 2007 13:06 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Sir,

But when I'm using replace function like

SQL>select replace(ename,'_',' ') from emp;

even though i'm getting the output like


replace(ename,'_
_________________________

SAM MIKE
NAGESH KAPOOR


But I'm unable to update permanently all the records.

so , sir can you throw light on this plz.

Regards,

Raj
Re: Removing underscores [message #254723 is a reply to message #254722] Fri, 27 July 2007 13:12 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Permanently? Well, to do that, you'll have to use UPDATE instead of SELECT. Don't forget to COMMIT after the update is done!
Re: Removing underscores [message #254728 is a reply to message #254408] Fri, 27 July 2007 13:34 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Instead of

select replace(table_name,'_',' ') from test;

do

update test
set table_name = select replace(table_name,'_',' ');


Make sure that you understand how to use the 3 main sql commands. I know that there are hundreds, but you must understand these to do anything with data.

UPDATE, DELETE, SELECT.
Re: Removing underscores [message #254729 is a reply to message #254723] Fri, 27 July 2007 13:36 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
sir,

can you plz tell me like how to update using the replace function. i have around 1500 records in that way and all need to be updated at one go.

I've tried but i'm getting an error.

so can you plz post the query.

Regards,

Raj
Re: Removing underscores [message #254730 is a reply to message #254408] Fri, 27 July 2007 13:37 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
We are not mind readers. Paste in what you tried and what the error is.

[Updated on: Fri, 27 July 2007 13:37]

Report message to a moderator

Re: Removing underscores [message #254731 is a reply to message #254728] Fri, 27 July 2007 13:38 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
sir,

Thanks a lot for helping me.
Re: Removing underscores [message #254740 is a reply to message #254729] Fri, 27 July 2007 14:59 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
rsreddy28 wrote on Fri, 27 July 2007 12:36
sir,

can you plz tell me like how to update using the replace function. i have around 1500 records in that way and all need to be updated at one go.

I've tried but i'm getting an error.

so can you plz post the query.

Regards,

Raj


post your querry and error.
Previous Topic: Distinct output
Next Topic: is "cursor for update " cause locking
Goto Forum:
  


Current Time: Sat Dec 03 01:36:10 CST 2016

Total time taken to generate the page: 0.08799 seconds