Re: Simple SQL question

From: <yong321_at_yahoo.com>
Date: 31 Jan 2006 13:31:57 -0800
Message-ID: <1138743117.193401.256750@g44g2000cwa.googlegroups.com>

Mahesh Rajendran wrote:
> -- In 9i
> --
> SCOTT > ed
> Wrote file afiedt.buf
>
> 1* select replace(replace('the cat sat on the mat',' ','
> '),' ',' ') from dual
> SCOTT > /
>
> REPLACE(REPLACE('THECA
> ----------------------
> the cat sat on the mat

The problem with this approach is you may not know how many layers of replace you need.

If you have a 10g database anywhere you can access and you just need to look at the data with a regular expression applied to it, simply create a database link from the 10g database to the remote 9i or older database (my misct shown below):

SQL> select * from t_at_misct;

S

---

a b c d

SQL> select regexp_replace(s, ' +', ' ') from t_at_misct;

REGEXP_REPLACE(S,'+','')

---

a b c d

A more common requirement is to update the table. You can "borrow" this 10g database for this task:

SQL> update t_at_misct set s = regexp_replace(s, ' +', ' '); update t_at_misct set s = regexp_replace(s, ' +', ' ')

                       *

ERROR at line 1:
ORA-02070: database MISCT does not support REGEXP_REPLACE in this context

SQL> begin
  2 for r in (select rowid rid, regexp_replace(s, ' +', ' ') new_s from t_at_misct) loop
  3 update t_at_misct set s = r.new_s where rowid = r.rid;   4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> select * from t_at_misct;

S

---

a b c d

The query against the remote table cannot have for update clause or you would get ORA-2070, so you can't use where current of in the update.

Yong Huang Received on Tue Jan 31 2006 - 15:31:57 CST