Comparing two fields 2

[jdgonzalez]

Hi everyone,

I'm a bit new to Oracle so I'm hoping someone can help me out with this. I have two columns that I'm trying to determine if they are correct. The problem is that one field contains a street address and the other contains the street address AND the city state and zip. So the data looks like this:

col a col b
101 main 101 main/anytown usa 12345

How can I check to see if they are the same. My inclination was to have a case statement like:

(case when col a like 'col b%' then 1 else 0 end) as address_match

When I use the above code it's not returning a match.

I'd appreciate any help.

Thanks

 

[carp]

Maybe something like:

SELECT cola, colb
FROM my_table
WHERE INSTR(colb, cola) > 0;

?

 

[SantaMufasa]

And if character case is not an issue, you can use this variation of Carp's excellent code:

SELECT cola, colb
FROM my_table
WHERE INSTR(upper(colb), upper(cola)) > 0;

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:17 (22Jul04) UTC (aka "GMT" and "Zulu"), 17:17 (21Jul04) Mountain Time)

 

[jdgonzalez]

Musafa and Carp,

Thanks for the code.