Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Monday, May 20, 2013 6:49 AM
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 9:30 AM ✅Answered
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:57 AM ✅Answered
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 6:51 AM
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 7:06 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:23 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:38 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:45 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 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.
Monday, May 20, 2013 8:14 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 4:38 PM
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