Home » SQL & PL/SQL » SQL & PL/SQL » replacing/removing characters from strings (merged similar topics from same user)
replacing/removing characters from strings (merged similar topics from same user) [message #283827] Wed, 28 November 2007 04:27 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

I am new to oracle i have a problem in my database,the problem is

name add sal

a abc 1000
b bc 2000
c xyzz 3000
d %nnn 4000 table is like that,what user want to see

name add sal

a abc 1000
b bc 2000
c xyzz 3000
d nnn 4000

how i can do this.
Re: problem removing '%' in table? [message #283832 is a reply to message #283827] Wed, 28 November 2007 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
replace

Regards
Michel
Re: problem removing '%' in table? [message #283839 is a reply to message #283827] Wed, 28 November 2007 04:53 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Wouldn't update work.
update table1 set add='nnn' where add='%nnn';
Re: problem removing '%' in table? [message #283845 is a reply to message #283827] Wed, 28 November 2007 05:18 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ dhanya (Varu123)

Update (your example) will work for the specific example given by OP. Michel's solution is more generalised .

Thumbs Up
Rajuvan.

[Updated on: Wed, 28 November 2007 05:18]

Report message to a moderator

Re: problem removing '%' in table? [message #283847 is a reply to message #283827] Wed, 28 November 2007 05:19 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Update will change the data parmanently. It just matter of display rather changing original data in database. But still the question araises why '%' there in 'nnn'.

Regards,
Kiran.
Re: problem removing '%' in table? [message #283854 is a reply to message #283827] Wed, 28 November 2007 05:35 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
@rajavu
Dhanya is my friends name.Actually its her ID.
So address with the ID name.
Re: problem removing '%' in table? [message #283859 is a reply to message #283847] Wed, 28 November 2007 05:37 Go to previous messageGo to next message
kakolz
Messages: 4
Registered: November 2007
Junior Member
Follow Michel's tip.

You will reach to something like this:
select replace('%nnn','%','') from dual
.
Just apply it to your case.

[Updated on: Wed, 28 November 2007 05:39]

Report message to a moderator

Re: problem removing '%' in table? [message #283869 is a reply to message #283859] Wed, 28 November 2007 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I don't give the solution this is with the purpose that OP search and find by himself.
Spoonfeeding just turns dumb the adressee.
Showing the way allows him to improve himself.

Regards
Michel
Re: problem removing '%' in table? [message #283870 is a reply to message #283827] Wed, 28 November 2007 05:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ok Varu123 ( i was not comfortable with such id Smile .

Select name,REPLACE(add,'%')add, sal  from table_name


will do the trick for you.But This will remove all the '%' from 'add' field which may lead to undesired result ( '%' can be present in address field Smile ). In that case Apply Ltrim and Rtrim to remove the '%' with prefix / suffix

Thumbs Up
Rajuvan.
Re: problem removing '%' in table? [message #284003 is a reply to message #283854] Wed, 28 November 2007 10:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
varu123 wrote on Wed, 28 November 2007 12:35

@rajavu
Dhanya is my friends name.Actually its her ID.
So address with the ID name.

Why don't you create your own account? It's free, and you won't get any spam or other stuff.
removing single quatation in database? [message #284312 is a reply to message #283827] Thu, 29 November 2007 06:23 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

In my database i have

customer name= mike O'Lee but user want to see mike O Lee,I want to remove the single quatation.

How i can remove that one,any one help me?
Re: removing single quatation in database? [message #284314 is a reply to message #284312] Thu, 29 November 2007 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
replace(field,'''',' ')


Regards
Michel

[Updated on: Thu, 29 November 2007 06:49]

Report message to a moderator

Re: removing single quatation in database? [message #284315 is a reply to message #284312] Thu, 29 November 2007 06:29 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

You want to just output on sqlplus or want to update in database ?
Re: removing single quatation in database? [message #284318 is a reply to message #284315] Thu, 29 November 2007 06:30 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
output,i don't have permissions to update database
Re: removing single quatation in database? [message #284321 is a reply to message #284312] Thu, 29 November 2007 06:35 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Second time you asking same type of question!?

http://www.orafaq.com/forum/m/283847/115564/#msg_283847

Regards,
Kiran.

[Updated on: Thu, 29 November 2007 06:37]

Report message to a moderator

icon11.gif  Re: removing single quatation in database? [message #284335 is a reply to message #284312] Thu, 29 November 2007 07:06 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

EveryDays Stuff
Problem in database? [message #284768 is a reply to message #283827] Fri, 30 November 2007 23:15 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

In my database customer name is mike ocorner.But user want to see the customer name as mike o'coner.
I don't have permission to update database.Which function help me to clear this one.

note: this customer name is example,in our database we want to change somany names like this.

[Updated on: Sat, 01 December 2007 01:03]

Report message to a moderator

Re: Problem in database? [message #284769 is a reply to message #284768] Fri, 30 November 2007 23:23 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

do you want this for just 1 user.if so, then you can use DECODE.


regards,
Re: Problem in database? [message #284778 is a reply to message #284769] Sat, 01 December 2007 01:04 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
not for this customer name ,we have somany customer names faceing same problem?
Re: Problem in database? [message #284779 is a reply to message #284778] Sat, 01 December 2007 01:11 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Use Replace Function.
Re: Problem in database? [message #284953 is a reply to message #284768] Mon, 03 December 2007 00:12 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

As i know 3rd time you asking same kind of question.

For your kind of reference, check this link once again.
http://www.orafaq.com/forum/m/284312/115564/#msg_284312
The thread was merged, because you had asked twice previously.

Regards,
Kiran.
Previous Topic: Prevent update on field value if no changes
Next Topic: get name of all computer on network
Goto Forum:
  


Current Time: Fri Dec 09 09:50:55 CST 2016

Total time taken to generate the page: 0.21399 seconds