Home » SQL & PL/SQL » SQL & PL/SQL » comparing two fields
comparing two fields [message #313913] Tue, 15 April 2008 03:01 Go to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear Friends

I am having a table with the following two columns and some sample data. In Bankaddress1 the bankname is repeated. Can anyone tell me of a way in which I can remove the Bankname from bankaddress1.

BANKNAME BANKADDRESS1

STATE BANK OF INDIA STATE BANK OF INDIA, GUDIYA
SYNDICATE BANK SYNDICATE BANK SOUTHRAM PART
INDIAN BANK INDIAN BANK NILAMBUR
ING VYSYA BANK 41, RAHMAN COMPLEX,
CORPORATION BANK CORPORATION BANK 100 FEET ROAD
STATE BANK OF INDIA STATE BANK OF INDIA
INDIAN OVERSEAS BANK INDIAN OVERSEAS BANK KANDIYUR
STATE BANK OF INDIA STATE BANK OF INDIA,SOUTH
INDIAN OVERSEAS BANK IOB, CRAWFORD,TRICHY-12
CORPORATION BANK CORPORATION BANK,V.KOOTU RD,


Thanking you


Alister
Re: comparing two fields [message #313914 is a reply to message #313913] Tue, 15 April 2008 03:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Probably with the replace command.

Although it's impossible to tell, since you didn't format your post in a readable manner.
Re: comparing two fields [message #313915 is a reply to message #313913] Tue, 15 April 2008 03:05 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Hi

I am sorry it did not upload properly. The Bankname and Bankaddress1 have been concatinated. ie 'STATE BANK OF INDIA STATE BANK OF INDIA'. One of them have to be removed.

Thanks


Alister

Re: comparing two fields [message #313916 is a reply to message #313913] Tue, 15 April 2008 03:05 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Format your post. It is currently unreadable. Use code tags to presever white space (you will find out how by reading the forum guide)
Re: comparing two fields [message #314195 is a reply to message #313913] Wed, 16 April 2008 00:58 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear Friends
I tried a little bit of formatting so please execuse the clumsyness.

BANKNAME , BANKADDRESS1
BBcode
STATE BANK OF INDIA
, STATE BANK OF INDIA, GUDIYA BBcode
SYNDICATE BANK
, SYNDICATE BANK SOUTHRAM PART BBcode
INDIAN BANK
, INDIAN BANK NILAMBUR BBcode
CORPORATION BANK
, CORPORATION BANK 100 FEET ROAD BBcode
STATE BANK OF INDIA
, STATE BANK OF INDIA BBcode
INDIAN OVERSEAS BANK
, INDIAN OVERSEAS BANK KANDIYUR BBcode
STATE BANK OF INDIA
, STATE BANK OF INDIA,SOUTH BBcode
CORPORATION BANK
, CORPORATION BANK,V.KOOTU RD,

The red letters should be removed from the second part. The red is one column (Bankname)and the black is the second column(
Bankaddress1).

Thank you

Alister

[Updated on: Wed, 16 April 2008 01:00]

Report message to a moderator

Re: comparing two fields [message #314202 is a reply to message #313913] Wed, 16 April 2008 01:23 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Have a look how others format their post ( use the "quote" button to have a peek Wink )

If I'm not misunderstanding you, this is what you are looking for:

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
Re: comparing two fields [message #314241 is a reply to message #313913] Wed, 16 April 2008 02:44 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear MarcS

Thank you so much for your patience and the code that you have given me. I shall try it out.

Thanks once again,


Alister
Re: comparing two fields [message #314243 is a reply to message #314241] Wed, 16 April 2008 02:47 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
alister wrote on Wed, 16 April 2008 09:44
Dear MarcS

Thank you so much for your patience and the code that you have given me. I shall try it out.

Thanks once again,


Alister



Just noticed that ThomasG provided the same answer as well
Re: comparing two fields [message #314495 is a reply to message #313913] Wed, 16 April 2008 22:46 Go to previous message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Quote:
Dear MarcS

Thank you so much. The code you have given me gave me the desired output. I think I got the formating right this time.


Thank you.

Alister
Previous Topic: Set column size
Next Topic: ORA-01033-Error
Goto Forum:
  


Current Time: Fri Dec 02 12:27:41 CST 2016

Total time taken to generate the page: 0.10679 seconds