SQL Server how to get money type's decimal dig

问题:

This question already has an answer here:

• Get the number of digits after the decimal point of a float (with or without decimal part) 6 answers

The column CostPrice of table t1 is money type.

The data in column CostPrice likes this:

141.1938
0.00
147.1041
119.592
1.23

I use this sql to get the decimal digits:

select distinct len(CostPrice-floor(CostPrice))-2 from t1;

But the result is only 2,this is not right,in fact,the result should be 2,3,4.

So how to fix the sql?

Added:

StackOverflow does not allow me to flag the flagging but asked me to edit the question instead, so:

This Question is not a duplicate to this one. The existing question is on the "float" datatype and this one is on "money", Converting "money" to "varchar" like in the "existing anser" will always return the same number of decimal places, so it does not answer this question.

回答1:

You could (independantly from regional settings):

• multiply the value by 10,000
• convert the result to integer
• convert the integer to a string
• add 4 leading zeroes to the left (just in case...)
• take the 4 characters from the right (the former decimal places)
• replace each zero by a blank character
• remove the trailing blanks using rtrim
• return the length of the remaining string

To put this in an expression, it would be:

LEN(RTRIM(REPLACE(RIGHT('0000' + CONVERT(varchar(20), CONVERT(int, CostPrice*10000)), 4), '0', ' ')))

回答2:

Try this

DECLARE 
@money money = 141.1938

 SELECT 
    @money
   ,SUBSTRING(CONVERT(varchar(20),@money,2), CHARINDEX('.', CONVERT(varchar(20),@money,2)) + 1, LEN(CONVERT(varchar(20),@money,2))) as RESULT