How to compare text column values ​​from one table

Question

I have a table in which each column represents text from a manuscript, here is a simple example:

mss1 | mss2 | mss3 ------------------------ The | The | A big | big | big black | | dog | dog | dog 

I would like to display rows in which two columns have different values ​​(or the same values), for example, I want to see the differences between mss1 and mss3. The result should look like this:

 mss1 | mss3 --------------- The | A black | 

These seemed to be good candidates for a solution:

 SELECT mss1, mss3 FROM table WHERE mss1 != mss3; SELECT mss1, mss3 FROM table WHERE mss1 NOT LIKE mss3; 

However, it does not work even after converting all columns from text to varchar of the same length.

I also tried LOCATE ( see here ) to find the same values: if I can find mss1 in mss3 and vice versa, they should have the same value, right? But this, too, was unsuccessful. Any ideas? It seems to be easy, but I can't figure it out ...

Answer 1

Perhaps the problem with your request is that the NULL values ​​are filtered out. For your purposes, I think this can do what you want:

 SELECT mss1, mss3 FROM table WHERE coalesce(mss1, '') <> coalesce(mss3, '');