Home » SQL & PL/SQL » SQL & PL/SQL » regexp_replace with clob (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
regexp_replace with clob [message #572743] Mon, 17 December 2012 04:10 Go to next message
manubatham20
Messages: 472
Registered: September 2010
Location: Champaign, IL
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
Re: regexp_replace with clob [message #572746 is a reply to message #572743] Mon, 17 December 2012 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
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
Re: regexp_replace with clob [message #572747 is a reply to message #572746] Mon, 17 December 2012 05:09 Go to previous message
manubatham20
Messages: 472
Registered: September 2010
Location: Champaign, IL
Senior Member

Thanks Michel.

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

Regards,
Manu
Previous Topic: Bulk insert into file
Next Topic: Rental Management ERD
Goto Forum:
  


Current Time: Fri Oct 24 18:56:33 CDT 2014

Total time taken to generate the page: 0.07656 seconds