CODEWITHSUNDEEP
sqlsql-servert-sql
Tayyab Amin
Tayyab Amin

Reputation: 139

SQL Server: Compare two columns

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

Upvotes: 3

Views: 130

Answers (3)

Coder221
Coder221

Reputation: 1433

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

Upvotes: 1

knkarthick24
knkarthick24

Reputation: 3216

One other way of doing it:

create  table #temp(col1 varchar(10),col2 decimal(10,6))
insert into #temp values(0.01,0.010000 ),(0.255,0.255000),(0.25,25),(0.555,10.0)
select * from #temp where REPLACE(REPLACE(col2,col1,''),0,'')  = ''
select * from #temp where REPLACE(REPLACE(col2,col1,''),0,'')  <> ''

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

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

Upvotes: 5

Related Questions