Returning values with more than two digits to the right of the decimal point

When inputting a number as a decimal, MS SQL Server pads the end of the number with 0s to equal the number of places declared in the scale. In other words, 27.543 is really 27.543000. To get around this, find the number of digits between the decimal point and the first zero.

Example:

declare @tbl_decimals table( 
        dec_column dec(13, 6)
) 

insert into @tbl_decimals 
values(123.1) 
insert into @tbl_decimals 
values(1234.12) 
insert into @tbl_decimals 
values(12345.123) 
insert into @tbl_decimals 
values(1234.1234) 

select * 
from @tbl_decimals 
WHERE len(left(right(dec_column, 6), 
   charindex('0', right(dec_column, 6)) - 1))
   > 2 
/* The number "6" should be replaced with the number 
indicated as the scale dec(precision, scale)*/ 

For More Information

• What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
• The Best SQL Web Links
• Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.