SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Parsing in SQL

  1. #1
    SitePoint Member
    Join Date
    May 2004
    Location
    Austin, TX
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Parsing in SQL

    You値l have to forgive me, I知 awfully green to this whole SQL thing. I have plenty of traditional programming experience, C, C++, Java and the like, but I知 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稚 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.

  2. #2
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


  3. #3
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Assuming you're using PHP, you can do something like this.
    PHP Code:
     $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
    Aaron Brazell
    Technosailor



  4. #4
    SitePoint Member
    Join Date
    May 2004
    Location
    Austin, TX
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure about PHP. Basically, I知 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稚 think PHP is embedded in the search (if that is a lunatic thing to say, that just reveals my astounding ignorance). I think I知 stuck using only what痴 available in the realm of SQL queries.

    As far as some nonsense background information, this is a new job. I知 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稚 find a job for 14 months, when I finally landed a job as a parking attendant, and now, finally I知 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稚.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Evil Deadite
    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 --
    Code:
    select left(Address1,10)
         , count(*) as howmany
      from Contact1
    group 
        by left(Address1,10)
    having count(*) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •