如何在SQL Server中以小数格式获取数字的小数部分

Hey Partha, nice catch with using PARSENAME to extract the decimal part—but I get why you need that value as a proper decimal (like 0.40) instead of just the integer 40. Let's go over two solid ways to get what you need:

Method 1: Numeric Subtraction (Recommended) #

The simplest and most efficient approach is to subtract the integer portion of your decimal value from the original number. This avoids string manipulation entirely, which is always better for numeric operations.

DECLARE @Total decimal(7,2) = 1000.40;
DECLARE @Remainder decimal(7,2) = 0;

-- Subtract the integer part (using FLOOR for positive values)
SET @Remainder = @Total - FLOOR(@Total);

SELECT @Remainder; -- Outputs 0.40 as a decimal(7,2)

This method works seamlessly even if @Total is a whole number (like 1000.00)—it'll just return 0.00 instead of a NULL, which is exactly what you want for consistent storage.

Method 2: Adjusting the PARSENAME Result #

If you specifically want to stick with PARSENAME, you can convert the extracted integer to a decimal and divide by 10 raised to the number of decimal places (in your case, 2, so divide by 100). Just make sure to handle cases where there's no decimal part to avoid NULLs:

DECLARE @Total decimal(7,2) = 1000.40;
DECLARE @Remainder decimal(7,2) = 0;

-- Convert the PARSENAME result to decimal and scale it down
SET @Remainder = CASE 
                    WHEN PARSENAME(@Total, 1) IS NOT NULL 
                    THEN CAST(PARSENAME(@Total, 1) AS decimal(7,2)) / 100 
                    ELSE 0.00 
                 END;

SELECT @Remainder; -- Outputs 0.40 as a decimal(7,2)

Quick Note #

Always prefer Method 1 when working with numeric values—it's faster, less error-prone, and aligns with how SQL Server handles decimal arithmetic natively.

内容的提问来源于stack exchange，提问作者Partha