# What's the best way to compare two numbers

### Question

• if you go two number a, and b.

a is from ur ms sql server, a float data type.

b is from other applications, unknown, could be float or decimal, real, numeric.

How to do the comparison to check two values are the same?

Sometimes, when you compare two numbers, u may find

`abs(a -b) > 0.00000001 `

is better than

`a=b`

Anyone know any articles or solution of number comparison?

Thanks.

Monday, May 20, 2013 6:49 AM

### Answers

• I'll have a stab at it. And it ain't pretty...

It also makes an important assumption about how you put your two values in. Since the data type cannot be assumes, I have used varchar(308)

```declare @a varchar(308)
declare @b varchar(308)

set @a='1.005123E+3'
set @b='1005.1230'

-- make sure all commas, dollar signs and
-- other unwanted characters are removed

Declare @sql     varchar(4000)
Declare @log_a   int
Declare @log_b   int
Declare @compare varchar(20)

Set @log_a = LOG10(CAST(@a AS float))+1
Set @log_b = LOG10(CAST(@b AS float))+1

If @log_a > 38 OR @log_b > 38
Set @compare='float'
Else
Begin
If @log_a > @log_b
Set @compare='decimal(38,'+CAST(38-@log_a AS char(2))+')'
Else
Set @compare='decimal(38,'+CAST(38-@log_b AS char(2))+')'
End

If @a LIKE '%E-[1-9][0-9]' OR @a LIKE  '%E-[0-3][0-9][0-9]'
Begin
If ABS(SUBSTRING(@a,LEN(@a)-2,3)) + CHARINDEX('E',LTRIM(@a)) -1 > 38
Set @compare='float'
End

If @b LIKE '%E-[1-9][0-9]' OR @b LIKE  '%E-[0-3][0-9][0-9]'
Begin
If ABS(SUBSTRING(@b,LEN(@b)-2,3)) + CHARINDEX('E',LTRIM(@b)) -1 > 38
Set @compare='float'
End

If @a LIKE '%E%' AND @compare<>'float'
Set @a='CAST('+@a+' AS float)'

If @b LIKE '%E%' AND @compare<>'float'
Set @b='CAST('+@b+' AS float)'

SELECT @sql = 'SELECT CASE WHEN CAST('+@a+' AS '+@compare+')'+CHAR(13)+CHAR(10)
+ '               = CAST('+@b+' AS '+@compare+')'+CHAR(13)+CHAR(10)
+ '            THEN ''equal'' ELSE ''not equal'' END'
select @sql
EXEC (@sql)

```

Gert-Jan

Monday, May 20, 2013 9:30 AM
•  if a.revenue is decial(24, 10) datatype, then the value would be truncated and the comparison is not accurate anymore.

If your CFO cares about fractional penny revenue, have the CEO fire him.

Best to convert it to decimal and compare.

```declare @a varchar(308)
declare @b varchar(308)

set @a='1.005123E+3'
set @b='1005.1230'

declare @da decimal (24,4) = convert(float,@a), @db decimal(24,4) = @b

select @da, @db

-- 1005.1230	1005.1230```

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Monday, May 20, 2013 9:57 AM

### All replies

• Read:

http://stackoverflow.com/questions/1632792/how-do-i-compare-two-columns-for-equality-in-sql-server

Many Thanks & Best Regards, Hua Min

Monday, May 20, 2013 6:51 AM
• I guess this not what I asked, but thanks.

I want to compare two numbers, and it is a bit complicated.

Monday, May 20, 2013 7:06 AM
• Read:

http://stackoverflow.com/questions/10330087/ms-sql-float-decimal-comparison-problems

Before doing the comparison, you need to do convert.

Read also:

Float is an approximation - great for hugely exponentiated numbers and scientists, not so good for accounting. It will convert properly, you just need to ascertain the scope or decimal places.

Use :  convert(decimal(18,6), a.revenue)    when you go to use it, such as :

SELECT A.EmpID,A.Revenue,B.EmpID,

B.Revenue,A.Revenue - B.Revenue AS Diff1,convert(decimal(18,6), a.revenue),convert(decimal(18,6), b.revenue),
convert(decimal(18,6), a.revenue) - convert(decimal(18,6), b.revenue) AS Diff2
FROM tableA A FULL JOIN tableB B ON A.EmpID = B.EmpID WHERE convert(decimal(18,6), a.revenue) <> convert(decimal(18,6), b.revenue)

Many Thanks & Best Regards, Hua Min

Monday, May 20, 2013 7:23 AM
• Thanks, a bit helpful.

However, when u do the conversion,if a.revenue data is from other organizations.

`convert(decimal(18,6), a.revenue) `

How do you know the data type for a.revenue, it could be float, int  decimal(10, 4) or decimal(20,10)

if a.revenue is decial(24, 10) datatype, then the value would be truncated and the comparison is not accurate anymore.

I want to know how to compare a know data type number with  an unknown data type number and what is the generic way, or good solution.

Hope I make it clear. :)

Monday, May 20, 2013 7:38 AM
• You can cast both numbers in whatever data type to Decimal(18,6), like

cast(a.revenue as decimal(18,6))

before doing the comparison.

Many Thanks & Best Regards, Hua Min

Monday, May 20, 2013 7:45 AM
• if you want to compare a and b,

a = 0.123456789

b = 0.1234567

in theory, a != b

when you convert both to decimal(18,6))

a = 0.123456

b= 0.123456

then a = b, which is incorrect.

I guess that it need some more scale.

What do your guys do to process it if you dont know b data type.

Monday, May 20, 2013 8:00 AM
• if you want to compare a and b,

a = 0.123456789

b = 0.1234567

in theory, a != b

when you convert both to decimal(18,6))

a = 0.123456

b= 0.123456

then a = b, which is incorrect.

I guess that it need some more scale.

What do your guys do to process it if you dont know b data type.

Then you can use decimal(23,11) instead.

Many Thanks & Best Regards, Hua Min

Monday, May 20, 2013 8:14 AM
• I'll have a stab at it. And it ain't pretty...

It also makes an important assumption about how you put your two values in. Since the data type cannot be assumes, I have used varchar(308)

```declare @a varchar(308)
declare @b varchar(308)

set @a='1.005123E+3'
set @b='1005.1230'

-- make sure all commas, dollar signs and
-- other unwanted characters are removed

Declare @sql     varchar(4000)
Declare @log_a   int
Declare @log_b   int
Declare @compare varchar(20)

Set @log_a = LOG10(CAST(@a AS float))+1
Set @log_b = LOG10(CAST(@b AS float))+1

If @log_a > 38 OR @log_b > 38
Set @compare='float'
Else
Begin
If @log_a > @log_b
Set @compare='decimal(38,'+CAST(38-@log_a AS char(2))+')'
Else
Set @compare='decimal(38,'+CAST(38-@log_b AS char(2))+')'
End

If @a LIKE '%E-[1-9][0-9]' OR @a LIKE  '%E-[0-3][0-9][0-9]'
Begin
If ABS(SUBSTRING(@a,LEN(@a)-2,3)) + CHARINDEX('E',LTRIM(@a)) -1 > 38
Set @compare='float'
End

If @b LIKE '%E-[1-9][0-9]' OR @b LIKE  '%E-[0-3][0-9][0-9]'
Begin
If ABS(SUBSTRING(@b,LEN(@b)-2,3)) + CHARINDEX('E',LTRIM(@b)) -1 > 38
Set @compare='float'
End

If @a LIKE '%E%' AND @compare<>'float'
Set @a='CAST('+@a+' AS float)'

If @b LIKE '%E%' AND @compare<>'float'
Set @b='CAST('+@b+' AS float)'

SELECT @sql = 'SELECT CASE WHEN CAST('+@a+' AS '+@compare+')'+CHAR(13)+CHAR(10)
+ '               = CAST('+@b+' AS '+@compare+')'+CHAR(13)+CHAR(10)
+ '            THEN ''equal'' ELSE ''not equal'' END'
select @sql
EXEC (@sql)

```

Gert-Jan

Monday, May 20, 2013 9:30 AM
•  if a.revenue is decial(24, 10) datatype, then the value would be truncated and the comparison is not accurate anymore.

If your CFO cares about fractional penny revenue, have the CEO fire him.

Best to convert it to decimal and compare.

```declare @a varchar(308)
declare @b varchar(308)

set @a='1.005123E+3'
set @b='1005.1230'

declare @da decimal (24,4) = convert(float,@a), @db decimal(24,4) = @b

select @da, @db

-- 1005.1230	1005.1230```

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Monday, May 20, 2013 9:57 AM
• I have been reprogramming a spreadsheet into sql.  I used a combination of relative and absolute error.  It isn't very pretty, but it works for me.  pretty much if the absolute or relative error is greater than expected, then return 0.

```CREATE FUNCTION [etl].[ValidateValue]
(
@Old				REAL,
@New				REAL,
@ErrorRelative		REAL,
@ErrorAbsolute		REAL,
@AllowZeroToNull	BIT
)
RETURNS BIT
AS
BEGIN

DECLARE @r BIT;

IF (@Old IS NOT NULL AND @New IS NOT NULL)
BEGIN

DECLARE @t TABLE(Actual BIT);

INSERT INTO @t(Actual) VALUES(CONVERT(BIT, CASE WHEN CASE WHEN @Old <> 0.0 THEN ABS(@Old - @New) / @Old * 100.0 ELSE 0.0 END <= @ErrorRelative THEN 1 ELSE 0 END));
INSERT INTO @t(Actual) VALUES(CONVERT(BIT, CASE WHEN ABS(@Old - @New) <= @ErrorAbsolute THEN 1 ELSE 0 END));

IF EXISTS (SELECT 1 FROM @t t WHERE t.Actual <> CONVERT(BIT, 1))
SET @r = 0;
ELSE
SET @r = 1;

END
ELSE
BEGIN

IF (@Old IS NULL AND @New IS NULL) OR (@AllowZeroToNull = 1 AND @Old = 0 AND @New IS NULL)
SET @r = 1;
ELSE
SET @r = 0;

END

RETURN @r

END```

And if you need 123412.213 == 123412.213 then specify 0.0 error (relative and absolute)

-r

hope this helps

Monday, May 20, 2013 4:38 PM