Comparing Columns In Posgre SQL

SEJC · #1 (**permalink**) 01-25-13, 17:19

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)

bibjet · #2 (**permalink**) 01-27-13, 18:29

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

Internet Services

Web Development

Digital Marketing

Consumer Tech