Parsing in SQL

[Evil Deadite]

You’ll have to forgive me, I’m awfully green to this whole SQL thing. I have plenty of traditional programming experience, C, C++, Java and the like, but I’m winging it with SQL. Basically trying to piece it together looking at samples and such. I bought a book, but left it at home today.

Anyway.

I have this query:

select Address1, count(*) where
from Contact1
group by Address1
having count(*) > 1

Which works great returning duplicate addresses. However, sometimes people input addresses different, you know, typing out Drive or abbreviating it. I want to write a looser version of this query that checks, say, the first ten characters and if they agree it counts it as a duplicate. Or, and I don’t know if this is possible, if I could insert code to parse the Address string and work from there.

Also, real quick, can I check information from two separate databases at once? Like select value from Data1 and data2?

Again, I apologize for the low level of the questions. I appreciate you time and any help, even a point in the right direction is appreciated.

[greg.harvey]

I don't know about returning the first 10 characters purely in SQL. If you can do two separate queries it's easy though, of course...

As for getting info from two separate tables, you can use JOIN, no?

http://dev.mysql.com/doc/mysql/en/JOIN.html

[Technosailor]

Assuming you're using PHP, you can do something like this.

 $add_bits = explode(' ',$address);
 /* Returns Array like
 $add_bits
   {
   0 => 123
   1 => Elm
   2 => Creek
   3 => Cir
   }
 */
 /* Assuming the Kind of road is the last element, do */
 $highest_key = count($add_bits); # Returns 4
 $road = strtolower($add_bits[$highest_key]);
 /* Parse through Possibilities */
 switch($road)
   {
   case 'rd':
   case 'road':
     $type = 'Road';
     break;
   case 'st':
   case 'str':
   case 'street':
     $type = 'Street':
     break;
   case 'cir':
   case 'cr':
   case 'circ':
   case 'circle':
     $type = 'Circle';
     break;
   /* Etc */
   } 

You May Want to do some error checking to make sure the last item is not a directional thing such as

2300 E St NW

But that can give you a jumping off point.

Aaron

[Evil Deadite]

Not sure about PHP. Basically, I’m using a program called GoldMine, some database program, and it has among its various database search choices and options a SQL search. The other searches it provides do not allow the flexibility I need. I don’t think PHP is embedded in the search (if that is a lunatic thing to say, that just reveals my astounding ignorance). I think I’m stuck using only what’s available in the realm of SQL queries.

As far as some nonsense background information, this is a new job. I’m working for a company that knows nothing about computers, programming and so forth. Whatsoever. At all. So they hired me on as an admin, found out I have an extensive programming background (I was a programmer, once upon a time, and then in ’92 I, and 75% of the rest of the company was laid off, I couldn’t find a job for 14 months, when I finally landed a job as a parking attendant, and now, finally I’m back in an office) and decided that I could do anything they need done on a computer. As much as I wish that was true, it isn’t.

[r937]

I bought a book, but left it at home today.

tee hee

"i'm a victim of soicumstance!!"

I want to write a looser version of this query that checks, say, the first ten characters and if they agree it counts it as a duplicate.

here you go --

select left(Address1,10)
     , count(*) as howmany
  from Contact1
group 
    by left(Address1,10)
having count(*) > 1