Simple query 1

[DotNetGnat]

Guys,

I am using oracle 10g and I have the following data in the two tables

table1

id | name
____________
1 |E 234TH RD
2 |E 12TH ST
3 |35TH AVE

table2

id | name
____________
100 |E 234 RD
200 |E 12 ST
300 |35 AVE

the tables here are shown in the simplest format....I am trying to do a match on name on both these tables.. and I need above 3 records to show up as matches...in table 1 "TH" has been removed but I still need to match it with the record in table 2 with "TH"

any suggestions...

thanks

-DNG

 

[SantaMufasa]

DNG,

If you can list all of the "rules" you want to follow in producing matches, then we can come up with code to make them match. For example:

Remove all "TH", "ND", "RD", "ST" ordinal indicators that immediately follow numerals.
"AV", "AVE" = "Avenue"
"RD" = "Road"
"ST" and "STR" = "Street"
et cetera

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DotNetGnat]

Thanks Dave,

As always your replies are wonderful...

i just this:

Remove all "TH", "ND", "RD", "ST" ordinal indicators that immediately follow numerals.

Thanks

-DNG

 

[DotNetGnat]

Dave,

I finally fixed it myself...as always thanks for your assistance...have a star...

-DNG

 

[SantaMufasa]

Geez, DNG, do I feel guilty...I thought that I had posted a coded response to your question. I see now that I didn't and that you created your own solution. You are the one that deserves the

instead of me.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[huchen]

Hello DNG, Can you post your solution here?