How do I match fields with different formats and data types?

Question

So if I have the following fields in two different tables

TABLE 1             |           TABLE 2
Itm_ID (VARCHAR2)   |           Itm_ID (NUMBER)                     
99-11-22            |           991122
12-33-44            |           123344
23-44-11            |           234411

...and I needed to match them to each other to compare data, how would I change the table 1 fields to update the table without dashes in the numbers and then match to the table 2 fields?

I know of to_number() so I think my biggest question is how I would update the table without the dashes in the Itm_ID.

Use the [SUBSTR function](http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions169.htm#SQLRF06114) to extract the numeric bits of the strings; use the [concatentation operator](http://docs.oracle.com/cd/B28359_01/server.111/b28286/operators003.htm#SQLRF51156) to tack the extracted bits together; then use [TO_NUMBER](http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions199.htm#SQLRF06140) to convert to resultant string to a number which can be compared to the other numbers. Best of luck. — Bob Jarvis - Слава Україні, Jul 06 '15 at 16:10
Oracle syntax : SELECT REPLACE('99-11-22','-','') FROM DUAL; — 027, Jul 06 '15 at 16:12
@Johnson Gale, Do you want the fields of table1 to be updated with fields of table2 by matching them? or anything else... — 027, Jul 06 '15 at 16:19

score 1 · Answer 1 · answered Jul 06 '15 at 16:25

1

To compare

WHERE TO_NUMBER(REPLACE(TABLE1.ITM_ID,'-',''))=TABLE2.ITM_ID

answered Jul 06 '15 at 16:25

Ravi

30,829
42
119
173

mahi_0707 · Answer 2 · 2015-07-06T18:35:14.447

Here you go, use a CASE to check values match or not !

Select TABLE1.ID,
TABLE1.Itm_ID ,
TABLE2.Itm_ID ,
CASE 
    WHEN TO_NUMBER (REPLACE(TABLE1.Itm_ID,'-','')) = TABLE2.Itm_ID 
    THEN 'MATCH'
    ELSE 'NO MATCH'
        END CASE
FROM
TABLE1 ,
TABLE2
where TABLE1 .id = TABLE2.id;

OUTPUT:

enter image description here

If you would like to permanently update table 1 then use below:

  update TABLE1
    set ITM_ID = TO_NUMBER (REPLACE(TABLE1.Itm_ID,'-','')); 
   -- It will remove '-' from all rows 

    COMMIT;

How do I match fields with different formats and data types?

2 Answers2