SQL*Plus: Release 10.1.0.5.0 - Production on Wo Apr 16 08:22:25 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> 
SQL> prompt Just a select to mimic the data
Just a select to mimic the data
SQL> 
SQL> select 'STATE BANK OF INDIA' col1	, 'STATE BANK OF INDIA, GUDIYA BBcode' col2
  2  from dual
  3  ;

COL1		    COL2
------------------- ----------------------------------
STATE BANK OF INDIA STATE BANK OF INDIA, GUDIYA BBcode

SQL> 
SQL> prompt Remove stuff of col1 from col2, notice there is still "rubbish" at the begining
Remove stuff of col1 from col2, notice there is still "rubbish" at the begining
SQL> 
SQL> select	replace(col2,col1,'')
  2  from (
  3  	 select 'STATE BANK OF INDIA' col1  , 'STATE BANK OF INDIA, GUDIYA BBcode' col2
  4  	 from dual
  5  	 )
  6  ;

REPLACE(COL2,CO
---------------
, GUDIYA BBcode

SQL> 
SQL> prompt Get rid of the "rubbish" as well
Get rid of the "rubbish" as well
SQL> 
SQL> select	ltrim(replace(col2,col1,''),', ')
  2  from (
  3  	 select 'STATE BANK OF INDIA' col1  , 'STATE BANK OF INDIA, GUDIYA BBcode' col2
  4  	 from dual
  5  	 )
  6  ;

LTRIM(REPLACE
-------------
GUDIYA BBcode

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options