none
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
    Moderator

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
    Moderator
  • 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