|
Home > Archive > Oracle certifications > April 2002 > Help me!!!
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| rajluvramya1 2002-04-10, 11:05 am |
| I have to change (data in)a column in a table which has single quotes within it, to * (like translate all 's to *s).
pls. send me sql query syntax.
 | |
| stecal 2002-04-10, 7:06 pm |
| Basic command is:
update tablename
set col=newvalue
where <some way you can identify what it is you want to change>;
Example:
update dept
set dname='ACCTING'
where deptno=10;
However, since you want to change a string contained within the table, your task is a bit more complex. You can use SQL to generate a SQL statement. Example:
select 'update dept set dname='||'''ACCOUNTING'''||' where deptno = 10;' from dual;
This outputs:
update dept set dname='ACCOUNTING' where deptno = 10;
Note the use of multiple single quotation marks around ACCOUNTING - those get nasty.
Use a text editor to replace the old with the new, save this as a sql file, then run it.
This should give you a good idea on what you can do to solve your problem. | |
| Igog_Pey 2002-04-11, 7:02 am |
| DECLARE
CURSOR c1 IS
SELECT u_column
FROM u_table
WHERE u_column LIKE '%''%'
FOR UPDATE;
current u_table.u_column%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO current
WHEN c1%NOTFOUND EXIT;
current = REPLACE(current, '''', '*');
UPDATE u_table SET u_column = current WHERE CURRENT OF c1;
END LOOP;
CLOSE c1;
END; | |
| rajluvramya1 2002-04-11, 7:34 am |
| Hi Igog_Pey,
Thanks a lot for the query...I'll try it and post a reply again if it works.
Good to see people helping eachother here.
Thanks again
Bye
Hi stecal,
Thanks to u too.
Bye
 |
|
|
|
|