OraFAQ Forum: SQL & PL/SQL » regexp

Home » SQL & PL/SQL » SQL & PL/SQL » regexp_replace with clob (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

regexp_replace with clob [message #572743]

Mon, 17 December 2012 04:10

manubatham20
Messages: 307
Registered: September 2010
Location: Noida, India

Senior Member

Hi,

I have a CLOB string like below:

/*****************************************/
/* Libname statements for data operators */
/*****************************************/
LIBNAME DMT "%sysget(CDR_BASE)/DMT";
LIBNAME SOURCE "%sysget(CDR_BASE)/SOURCE";
LIBNAME Target "%sysget(CDR_BASE)/Target";


/****************************************************/
/* Libname statement used to establish a connection */
/* to the database in case of connected mode.       */
/* (data operators are build on pass-through views  */
/*  using all the same connection)                  */
/****************************************************/
%MACRO  createCDRDBMSlib;
%if &SYSVER = 9.2 %then %do;
LIBNAME CDR_DBMS ORACLE PATH = "PROD.ABCD.CORP.XYZ.IN" 
DBPROMPT=YES DEFER=NO USER=LSHADMIN 
 UPDATE_LOCK_TYPE=ROW PRESERVE_TAB_NAMES=YES  ADJUST_BYTE_SEMANTIC_COLUMN_LENGTHS=NO;
%end;
%else %do;
LIBNAME CDR_DBMS ORACLE PATH = "PROD.ABCD.CORP.XYZ.IN" 
DBPROMPT=YES DEFER=NO USER=LSHADMIN 
 UPDATE_LOCK_TYPE=ROW PRESERVE_TAB_NAMES=YES ;
%end;
%MEND createCDRDBMSlib;

%createCDRDBMSlib;


/****************************************************************************/
/* SAS views to CDR Oracle tables as source dataoperators in connected mode */
/****************************************************************************/

I am trying with regexp_replace in above clob to replace
[some text before]
LIBNAME CDR_DBMS something
something
something;
[some text after]

with
[some text before]
Manu;
[some text after]

Note: String starting with "LIBNAME CDR_DBMS" and ending with a ";" should be replaced with "Manu;"
on all the places, but not able to achive that.

create table test (a clob);

insert into test values (
'/*****************************************/'||chr(10)||
'/* Libname statements for data operators */'||chr(10)||
'/*****************************************/'||chr(10)||
'LIBNAME DMT "%sysget(CDR_BASE)/DMT";'||chr(10)||
'LIBNAME SOURCE "%sysget(CDR_BASE)/SOURCE";'||chr(10)||
'LIBNAME Target "%sysget(CDR_BASE)/Target";'||chr(10)||chr(10)||chr(10)||
'/****************************************************/'||chr(10)||
'/* Libname statement used to establish a connection */'||chr(10)||
'/* to the database in case of connected mode.       */'||chr(10)||
'/* (data operators are build on pass-through views  */'||chr(10)||
'/*  using all the same connection)                  */'||chr(10)||
'/****************************************************/'||chr(10)||
'%MACRO  createCDRDBMSlib;'||chr(10)||
'%if SYSVER = 9.2 %then %do;'||chr(10)||
'LIBNAME CDR_DBMS ORACLE PATH = "PROD.ABCD.CORP.XYZ.IN" '||chr(10)||
'DBPROMPT=YES DEFER=NO USER=LSHADMIN '||chr(10)||
' UPDATE_LOCK_TYPE=ROW PRESERVE_TAB_NAMES=YES  ADJUST_BYTE_SEMANTIC_COLUMN_LENGTHS=NO;'||chr(10)||
'%end;'||chr(10)||
'%else %do;'||chr(10)||
'LIBNAME CDR_DBMS ORACLE PATH = "PROD.ABCD.CORP.XYZ.IN" '||chr(10)||
'DBPROMPT=YES DEFER=NO USER=LSHADMIN '||chr(10)||
' UPDATE_LOCK_TYPE=ROW PRESERVE_TAB_NAMES=YES ;'||chr(10)||
'%end;'||chr(10)||
'%MEND createCDRDBMSlib;'||chr(10)||chr(10)||
'%createCDRDBMSlib;'||chr(10)||chr(10)||
'/****************************************************************************/'||chr(10)||
'/* SAS views to CDR Oracle tables as source dataoperators in connected mode */'||chr(10)||
'/****************************************************************************/'
);

---I am trying like below
select regexp_replace(a, 'LIBNAME CDR_DBMS*;$', 'Manu') from test;

Output should be:

/*****************************************/
/* Libname statements for data operators */
/*****************************************/
LIBNAME DMT "%sysget(CDR_BASE)/DMT";
LIBNAME SOURCE "%sysget(CDR_BASE)/SOURCE";
LIBNAME Target "%sysget(CDR_BASE)/Target";


/****************************************************/
/* Libname statement used to establish a connection */
/* to the database in case of connected mode.       */
/* (data operators are build on pass-through views  */
/*  using all the same connection)                  */
/****************************************************/
%MACRO  createCDRDBMSlib;
%if &SYSVER = 9.2 %then %do;
Manu;
%end;
%else %do;
Manu;
%end;
%MEND createCDRDBMSlib;

%createCDRDBMSlib;


/****************************************************************************/
/* SAS views to CDR Oracle tables as source dataoperators in connected mode */
/****************************************************************************/

Please help.

Thanks,
Manu

Report message to a moderator

Re: regexp_replace with clob [message #572746 is a reply to message #572743]

Mon, 17 December 2012 05:04

Michel Cadot
Messages: 54124
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks for the test case.

SQL> select 
  2    regexp_replace (
  3      a, 
  4      'LIBNAME CDR_DBMS[^'||chr(10)||']*'||chr(10)||'[^'||chr(10)||']*'||chr(10)||'[^'||chr(10)||']*'||chr(10),
  5      'Manu;'||chr(10)
  6    ) res
  7  from test;
RES
------------------------------------------------------------------------------------------------------------------
/*****************************************/
/* Libname statements for data operators */
/*****************************************/
LIBNAME DMT "%sysget(CDR_BASE)/DMT";
LIBNAME SOURCE "%sysget(CDR_BASE)/SOURCE";
LIBNAME Target "%sysget(CDR_BASE)/Target";


/****************************************************/
/* Libname statement used to establish a connection */
/* to the database in case of connected mode.       */
/* (data operators are build on pass-through views  */
/*  using all the same connection)                  */
/****************************************************/
%MACRO  createCDRDBMSlib;
%if SYSVER = 9.2 %then %do;
Manu;
%end;
%else %do;
Manu;
%end;
%MEND createCDRDBMSlib;

%createCDRDBMSlib;

/****************************************************************************/
/* SAS views to CDR Oracle tables as source dataoperators in connected mode */
/****************************************************************************/

1 row selected.

Regards
Michel

Report message to a moderator

Re: regexp_replace with clob [message #572747 is a reply to message #572746]

Mon, 17 December 2012 05:09

manubatham20
Messages: 307
Registered: September 2010
Location: Noida, India

Senior Member

Thanks Michel.

It's a noticable thing that now your avatar looks nice. Smile

Regards,
Manu

Report message to a moderator

Previous Topic:	Bulk insert into file
Next Topic:	Rental Management ERD

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 04:49:03 CDT 2013

Total time taken to generate the page: 0.16801 seconds