How to compare two columns in SQL server

Question

I have two columns in SQL Server in two different tables. One column has 9.011, and other table columns has 9011. I need to remove the . and compare these two columns to see whether they are equal.

Can anybody help me out how to do this?

Thanks in advance.

Answer 1

Try this:

SELECT CASE WHEN REPLACE (Table1.ColName1,'.','') = Table2.ColName2 
            THEN 'Equal' 
            ELSE 'Not Equal' 
            END AS IsEqual
    FROM Table1
    INNER JOIN Table2 ON Table1.PrimaryKey = Table2.ForeignKey

This query will return Equal if they are equal and Not Equal if they are not.

REPLACE() will remove . from ColName1.

Answer 2

I am assuming that your columns are decimal type, so I have converted them to varchar first

where 
replace(Convert(varchar(50), column1 ),'.','') = Convert(varchar(50), column2)