How to match two tables having different data formet

[ancheswaroop]

I have to match two tables with one columns
but columns are with different data formet
columns 1 having data as +000000150.20 and
columns 2 data as 000150.20

data type of this columns=char

how can i match this two tables

 

[carp]

If the formats are consistent throughout the columns, then you could join with

WHERE SUBSTR(column1, -9,9) = column2;

 

[ancheswaroop]

//(column1, -9,9)//
what is this

 

[carp]

SUBSTR(column1, -9,9) is a call to the substring function.
column1 is the name of your column.
-9 is the position in column1 that is 9 characters from the rightmost end to the string. 9 is the number of characters you want in your string.

When in doubt about syntax, you can (and really should) look up the details for the function in your Oracle reference manual. This way, you will learn more about Oracle in a shorter amount of time.

 

[SantaMufasa]

Ancheswaroop,

Can you please confirm the following:

1) What are the column definitions for "column1" and "column2"? Are they NUMBER, VARCHAR, CHAR, CLOB?

2) For your puposes, are the values "150.2" and "+000000150.20" equal? (If so, then you are looking for algebraic/arithmetic/numeric equality; if they are not equal, then you are looking for bit/character equality.)

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]