Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Regular Expressions like feature in 9i

Sreekanth MunagalaJul 2 2009 — edited Jul 2 2009
Hi Friends,
The sample data stored in one of the columns in the database is as follows: 
SELECT *
FROM
(
SELECT 'MTC-1039195130-Elcoteq' location FROM DUAL UNION ALL
SELECT 'MTC Foxconn India' FROM DUAL UNION ALL
SELECT 'MTC_Hosiden_1798467' FROM DUAL UNION ALL
SELECT 'EMS- MTC-QISDA CORPORATION' FROM DUAL UNION ALL
SELECT 'EMS MTC-BANSHING HKG' FROM DUAL 
)
In all these,i want to replace MTC with XXX.

Database Version:Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

Comments

ravikumar.sv 
SELECT replace(location,'MTC','XXX')
FROM
(
SELECT 'MTC-1039195130-Elcoteq' location FROM DUAL UNION ALL
SELECT 'MTC Foxconn India' FROM DUAL UNION ALL
SELECT 'MTC_Hosiden_1798467' FROM DUAL UNION ALL
SELECT 'EMS- MTC-QISDA CORPORATION' FROM DUAL UNION ALL
SELECT 'EMS MTC-BANSHING HKG' FROM DUAL 
)
use replace function.

Ravi Kumar
SKavi
Are you sure MTC will not appear any where else in the text again.. and always it's going to be upper case only?
if so.. try the option of replace..

Edited by: Srichan on Jul 2, 2009 5:22 PM
Walter Fernández
Hi,

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as hr
SQL> 
SQL> with data as
  2  (
  3  SELECT 'MTC-1039195130-Elcoteq' location FROM DUAL UNION ALL
  4  SELECT 'MTC Foxconn India' FROM DUAL UNION ALL
  5  SELECT 'MTC_Hosiden_1798467' FROM DUAL UNION ALL
  6  SELECT 'EMS- MTC-QISDA CORPORATION' FROM DUAL UNION ALL
  7  SELECT 'EMS MTC-BANSHING HKG' FROM DUAL
  8  )
  9  select replace(location, 'MTC', 'XXX') FROM DATA;

REPLACE(LOCATION,'MTC','XXX')
------------------------------------------------------------------------------
XXX-1039195130-Elcoteq
XXX Foxconn India
XXX_Hosiden_1798467
EMS- XXX-QISDA CORPORATION
EMS XXX-BANSHING HKG

SQL>
Regards,
Sreekanth Munagala
Hi Ravi,
MTC should be replaced like below in the following scenarios
ii)abcMTC MTC -->abcMTC XXX
iii)abcmtc mtc -->abcmtc XXX
iii)abc-mtc-zzz -->abc-XXX-zzz
iii) It would be better,if more than one space is present,all the spaces can be reduced to one space.ex abc MTC dfg -->abc XXX dfg
Walter Fernández
Hi,
Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0 
Connected as hr


SQL> 
SQL> with data as
  2      (
  3      SELECT 'abcMTC MTC' location FROM DUAL UNION ALL
  4      SELECT 'abcmtc mtc' FROM DUAL UNION ALL
  5      SELECT 'abc-mtc-zzz' FROM DUAL UNION ALL
  6      SELECT 'EMS- MTC-QISDA CORPORATION' FROM DUAL UNION ALL
  7      SELECT 'EMS MTC-BANSHING HKG' FROM DUAL
  8      )
  9      select location, replace(replace(replace(location, ' MTC', ' XXX'), ' mtc', ' XXX'), '-MTC', '-XXX') FROM DATA;

LOCATION                   REPLACE(REPLACE(REPLACE(LOCATI
-------------------------- --------------------------------------------------------------------------------
abcMTC MTC                 abcMTC XXX
abcmtc mtc                 abcmtc XXX
abc-mtc-zzz                abc-mtc-zzz
EMS- MTC-QISDA CORPORATION EMS- XXX-QISDA CORPORATION
EMS MTC-BANSHING HKG       EMS XXX-BANSHING HKG

SQL>
Regards,
MichaelS
Or maybe
SQL>  BEGIN
   FOR c IN (SELECT 'aabcMTC MTC' location FROM DUAL
             UNION ALL
             SELECT 'abcmtc mtc' FROM DUAL
             UNION ALL
             SELECT 'abc-mtc-zzz' FROM DUAL)
   LOOP
      owa_pattern.change (c.location, '([ -])mtc', '\1XXX', 'i');
      dbms_output.put_line (c.location);
   END LOOP;
END;
/
aabcMTC XXX
abcmtc XXX
abc-XXX-zzz
PL/SQL procedure successfully completed.
?
Sreekanth Munagala
Hi Michael,
This will be helpful.
can you please explain this or give me a link for the documentation.
MichaelS
Check the OWA_PATTERN documentation. If there are still questions, just come back ;)
riedelme
In all these,i want to replace MTC with XXX.
Since the data is hard-coded why not just change the code replacing MTC with XXX?
Hoek
OWA_PATTERN is not documented for 9i:

http://www.oracle.com/pls/db92/db92.drilldown?remark=&word=owa_pattern&book=&preference=

but it is for 10G:

http://www.oracle.com/pls/db102/search?remark=quick_search&word=owa_pattern&tab_id=&format=ranked
Aketi Jyuuzou
There is a good news B-)

owa_pattern is documented in Oracle9i ;-)
http://search.oracle.com/search/search?default=true&start=1&group=All&showSimilarDoc=true&keyword=owa_pattern

http://download.oracle.com/docs/cd/A97329_03/web.902/a90101/pspatt.htm
1 - 11
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 30 2009
Added on Jul 2 2009
11 comments
6,334 views