0

I am using the except syntax in postgreSQL to check if the addresses from the two table matches and below are the values that are not matching.

select Address from allsalesdata
except
select Address from addresses

I have copied the missing addresses and pasted into Excel to check against the data source (addresses) and I cannot spot the differences. I have checked both, if and exact formulas in Excel to check if the two values are matching and the answer was in negative. How to spot the differences between the two column values in SQL?

    allsalesdata                         addresses

111 Thoroughbred Avenue         111 Thoroughbred Avenue 
222 Bannerdale Blvd             222 Bannerdale Blvd 
3333 Stampede Drive             3333 Stampede Drive 
444 Stampede Drive              444 Stampede Drive 
555 Thoroughbred Avenue         555 Thoroughbred Avenue
Jacob_Cortese
  • 139
  • 1
  • 4
  • 15

2 Answers2

1

Try using trim

trim([leading | trailing | both] [characters] from string)

Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string

select trim(both ' ' from Address) as Address from allsalesdata
except
select trim(both ' ' from Address) as Address from addresses
McNets
  • 10,352
  • 3
  • 32
  • 61
1

As McNets points out, you can remove the leading and trailing spaces as he mentions. If you have more than just blank spaces out there, then you may want to use the regex capability as described in this answer

If what you want is Spot the differences for later processing, I recommend writing the "differences" table (select except select) to a csv with quotations enabled so you can use a text editor or linux command to spot those pesky characters.

Mematematica
  • 63
  • 6