SQL Server: Compare two columns

Question

I have two columns in a SQL table as follow. I need to compare these two columns for mismatches but due to extra decimals i am getting false results. When i try to convert the first column it gives the error

"Error converting data type varchar to numeric."

How to solve this issue? The length of first column varies.

Column01(varchar)   Column02(Decimal)
0.01                0.010000  
0.255               0.255000

Answer 1

You have data in Column01 that cannot be casted to DECIMAL.

With SQL Server 2012+ I would use TRY_PARSE:

SELECT *
FROM your_table
WHERE Column02 = TRY_PARSE(Column01 AS DECIMAL(38,18));

LiveDemo

When value from column cannot be casted safely you get NULL.

For SQL Server 2008 you can use:

SELECT *
FROM #tab
WHERE (CASE 
          WHEN ISNUMERIC(Column01) = 1 THEN CAST(Column01 AS DECIMAL(38,18))
          ELSE NULL
      END) = Column02;

EDIT:

If you need it at column level use:

SELECT Column01, Column02,
   CASE WHEN Column02 = TRY_PARSE(Column01 AS DECIMAL(38,18))
          OR (Column02 IS NULL AND Column01 IS NULL)
        THEN 'true'
        ELSE 'false'
    END AS [IsEqual]
FROM #tab;

LiveDemo2

Answer 2

You can do this using self join and conversion function

SELECT x.Column01, y.Column02
FROM table1 x, table1 y
WHERE x.Column02 = try_parse(y.Column01 as decimal(38,18))

Since I cannot comment, I like to thank lad2025 for showing live demo and introducing to data.stackexchange for composing queries