Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2013
    Posts
    1

    Comparing Columns In Posgre SQL

    Greetings,

    I am trying to weed out bad data in a Address table. I have an instance where
    Column Address3 imported City, state, and zip and Column Zip also imported the zip code in random cases. I need to get rid of the zip code in these random cases. I want to run a comparison of Address3 and Zip to see where Address3 is LIKE Zip or vise versa so I can isolate the data. For example:

    Column Address 3
    is
    Watertown, NY 15674

    Column ZIP
    is
    15674

    What would be a query that would compare these? Thanks! X)

  2. #2
    Join Date
    Dec 2012
    Posts
    32
    Guessing ...:
    Do you want to compare the five last characters from column "Address3" with column "ZIP" and you want all lines where these a different?
    Assuming that column "ZIP" is a text or varchar column.
    In the examlpe below you have to replace "Your_Table_Name" with the right table name of course.

    Code:
    SELECT Address3,ZIP 
     FROM Your_Table_Name
     WHERE substr(Address3,length(Address3)-4,5) <> ZIP;
    See also the related functions in the official PostgreSQL documentation:
    PostgreSQL: Documentation: 9.1: String Functions and Operators

Posting Permissions

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