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.

Solution 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.

Solution 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)

Solution 3

Casting the numbers as strings, cleaning up the decimal point, and comparing should work for numeric types with scale & precision to capture all the digits to compare.

For example, the following PoC in T-SQL...

DECLARE @foo float = 9.011;
DECLARE @bar int = 9011;
--Yup!
--DECLARE @foo decimal(4,3) = 9.011;
--DECLARE @bar decimal(4) = 9011;
----Yup!
--DECLARE @foo numeric(4,3) = 9.011;
--DECLARE @bar numeric(4) = 9011;
----Yup!
--...etcetera.

DECLARE @fooString varchar(max) = CAST(@foo AS varchar(max));
DECLARE @barString varchar(max) = CAST(@bar AS varchar(max));

PRINT '@fooString = ' + @fooString;
PRINT '@barString = ' + @barString;

IF (REPLACE(@fooString, '.', '') = @barString
    PRINT 'Yup!';
ELSE
    PRINT 'Nope....';

...yields the following output:

@fooString = 9.011
@barString = 9011
Yup!

No, you would not need varchar(max) of course: just set more reasonable string lengths for your needs.

Comments

[J0e3gan]

float & int, decimal(4,3) & decimal(4,0), numeric(4,3) & numeric(4,0) (given the numbers in question) etcetera should all be fine with this approach - so long as their scale & precision capture the digits to compare as strings of course.

[J0e3gan]

I would still prefer to be explicit in this case, but that is a very nice use of implicit conversion.