要如何取出數值小數點後面的值呢?
本文記錄SQL SERVER – Different Methods to Extract Scale Part From Decimal Number所說的3種方法!
以下介紹「SQL SERVER – Different Methods to Extract Scale Part From Decimal Number」這篇，
在SQL Server中，如何取出數值的小數點後面的數值呢?
例如有個數值是 12.345 ，要如何取出 345 呢?
1.減掉整數再取出來
DECLARE @value DECIMAL(16, 3);
SET @value = 12.345;
SELECT @value as orgValue,
REPLACE(STR(ABS(@value - CAST(@value AS INT)), 8, 3), '0.', '') as way1;
2.用 SUBSTRING 去取
DECLARE @value DECIMAL(16, 3);
SET @value = 12.345;
SELECT @value as orgValue,
SUBSTRING(STR(@value, 16, 3), CHARINDEX('.', STR(@value, 16, 3)) + 1,
LEN(@value)) as way2;
3.使用 PARSENAME ，它會用.去Split，所以取1就是小數點後的值，2就是整數的值哦!
DECLARE @value DECIMAL(16, 3);
SET @value = 12.345;
SELECT @value as orgValue,
PARSENAME(@value, 1) as way3;
用 PARSENAME 居然也可以，真是蠻奇妙的…
參考資料
SQL SERVER – Different Methods to Extract Scale Part From Decimal Number
Get precision and decimal value in SQL Server by using PARSENAME
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
