| replace special characters ',",%,& in oracle 9i [message #13889] |
Sun, 01 August 2004 05:41  |
farooq
Messages: 12
Registered: July 2001
|
Junior Member |
|
|
Hi everybody there
good morning
still i am unable to remove or replace single quotes(') ,double quotes("),and some special characters(&,% etc) from my database i.e oracle 9i,i have a table name item_master in which item_name is the field name which has thousands of records containing text like
item_name
------------
ring of 3 "
item 2 " & 3'
6%of data
now whenever my jsp page comes across such type of data it is throwing error as command not properly ended ,and even whenever i try to generate some pdf document out of these data it gives error,thats why i decided to remove all the special characters from the data base please if possible try to reply me with exact queries
thank you
|
|
|
|
| Re: replace special characters ',",%,& in oracle 9i [message #13890 is a reply to message #13889] |
Sun, 01 August 2004 13:09  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You should be able to replace all occurrences of each identified character as below. You can do individual updates or one combined update with multiple replaces, as I have demonstrated below. If this does not help, then please post a similar example of starting data, your attempted update, and the results.
-- starting data:
scott@ORA92> select * from item_master
2 /
ITEM_NAME
------------------------------
ring of 3 "
item 2 " & 3'
6%of data
-- upate:
scott@ORA92> set define off
scott@ORA92> update item_master
2 set item_name = replace (replace (replace
3 (item_name, '"', 'inch'),
4 '&', 'and'),
5 '%', 'percent')
6 /
3 rows updated.
-- ending data:
scott@ORA92> select * from item_master
2 /
ITEM_NAME
------------------------------
ring of 3 inch
item 2 inch and 3'
6percentof data
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
