How do I get only the numbers after the decimal?
如何只得到小数后面的数?
Example: 2.938
= 938
例如:2.938 = 938
10 个解决方案
#1
45
one way, works also for negative values
一种方法,也适用于负值
declare @1 decimal(4,3)
select @1 = 2.938
select PARSENAME(@1,1)
#2
110
try this:
试试这个:
SELECT (num % 1)
#3
12
You can use FLOOR
:
您可以使用地板:
select x, ABS(x) - FLOOR(ABS(x))
from (
select 2.938 as x
) a
Output:
输出:
x
-------- ----------
2.938 0.938
Or you can use SUBSTRING
:
或者可以使用子字符串:
select x, SUBSTRING(cast(x as varchar(max)), charindex(cast(x as varchar(max)), '.') + 3, len(cast(x as varchar(max))))
from (
select 2.938 as x
) a
#4
4
The usual hack (which varies a bit in syntax) is
通常的hack(在语法上有所不同)是。
x - floor(x)
That's the fractional part. To make into an integer, scale it.
小数部分。要使其成为整数,请对其进行缩放。
(x - floor(x)) * 1000
#5
3
More generalized approach may be to merge PARSENAME and % operator. (as answered in two of the answers above)
更广义的方法可能是合并PARSENAME和%运算符。(如以上两个答案所述)
Results as per 1st approach above by SQLMenace
结果,按第1方法,由sql威胁。
select PARSENAME(0.001,1)
Result: 001
结果:001
select PARSENAME(0.0010,1)
Result: 0010
结果:0010
select PARSENAME(-0.001,1)
Result: 001
结果:001
select PARSENAME(-1,1)
Result: -1 --> Should not return integer part
结果:-1——>不应该返回整数部分。
select PARSENAME(0,1)
Result: 0
结果:0
select PARSENAME(1,1)
Result: 1 --> Should not return integer part
结果:1——>不返回整数部分
select PARSENAME(100.00,1)
Result: 00
结果:00
Results as per 1st approach above by Pavel Morshenyuk "0." is part of result in this case.
根据上面帕维尔·莫申约克“0”的第1种方法得出的结果是这种情况下的结果的一部分。
SELECT (100.0001 % 1)
Result: 0.0001
结果:0.0001
SELECT (100.0010 % 1)
Result: 0.0010
结果:0.0010
SELECT (0.0001 % 1)
Result: 0.0001
结果:0.0001
SELECT (0001 % 1)
Result: 0
结果:0
SELECT (1 % 1)
Result: 0
结果:0
SELECT (100 % 1)
Result: 0
结果:0
Combining both:
结合这两个:
SELECT PARSENAME((100.0001 % 1),1)
Result: 0001
结果:0001
SELECT PARSENAME((100.0010 % 1),1)
Result: 0010
结果:0010
SELECT PARSENAME((0.0001 % 1),1)
Result: 0001
结果:0001
SELECT PARSENAME((0001 % 1),1)
Result: 0
结果:0
SELECT PARSENAME((1 % 1),1)
Result: 0
结果:0
SELECT PARSENAME((100 % 1),1)
Result: 0
结果:0
But still one issue which remains is the zero after the non zero numbers are part of the result (Example: 0.0010 -> 0010). May be one have to apply some other logic to remove that.
但是仍然有一个问题仍然存在,那就是非零数之后的0是结果的一部分(示例:0.0010 -> 0010)。也许你必须运用一些其他的逻辑来去除它。
#6
0
If you know that you want the values to the thousandths, place, it's
如果你想要的值是千分位
SELECT (num - FLOOR(num)) * 1000 FROM table...;
#7
0
Make it very simple by query:
通过查询使其非常简单:
select substr('123.123',instr('123.123','.')+1, length('123.123')) from dual;
Put your number or column name instead 123.122
把你的号码或列名改为123.122
#8
0
If you want to select only decimal numbers use this WHERE clause:
如果你想只选择十进制数字使用这个WHERE子句:
(CAST(RIGHT(Myfield, LEN( Myfield)-CHARINDEX('.',Myfield)+1 ) AS FLOAT)) <> 0
If you want a clear list you can sort by decimal/integer:
如果你想要一个清晰的列表,你可以按十进制/整数排序:
CASE WHEN 0 = CAST(RIGHT(Myfield, LEN( Myfield)-CHARINDEX('.',Myfield)+1 ) AS FLOAT) THEN 'Integer' ELSE 'Decimal' END AS Type
#9
-1
You can use RIGHT :
你可以使用权利:
select RIGHT(123.45,2) return => 45
#10
-1
CAST(RIGHT(MyField, LEN( MyField)-CHARINDEX('.',MyField)+1 ) AS FLOAT)
将(右(MyField, LEN(MyField)-CHARINDEX('.',MyField)+1)转换为FLOAT)
#1
45
one way, works also for negative values
一种方法,也适用于负值
declare @1 decimal(4,3)
select @1 = 2.938
select PARSENAME(@1,1)
#2
110
try this:
试试这个:
SELECT (num % 1)
#3
12
You can use FLOOR
:
您可以使用地板:
select x, ABS(x) - FLOOR(ABS(x))
from (
select 2.938 as x
) a
Output:
输出:
x
-------- ----------
2.938 0.938
Or you can use SUBSTRING
:
或者可以使用子字符串:
select x, SUBSTRING(cast(x as varchar(max)), charindex(cast(x as varchar(max)), '.') + 3, len(cast(x as varchar(max))))
from (
select 2.938 as x
) a
#4
4
The usual hack (which varies a bit in syntax) is
通常的hack(在语法上有所不同)是。
x - floor(x)
That's the fractional part. To make into an integer, scale it.
小数部分。要使其成为整数,请对其进行缩放。
(x - floor(x)) * 1000
#5
3
More generalized approach may be to merge PARSENAME and % operator. (as answered in two of the answers above)
更广义的方法可能是合并PARSENAME和%运算符。(如以上两个答案所述)
Results as per 1st approach above by SQLMenace
结果,按第1方法,由sql威胁。
select PARSENAME(0.001,1)
Result: 001
结果:001
select PARSENAME(0.0010,1)
Result: 0010
结果:0010
select PARSENAME(-0.001,1)
Result: 001
结果:001
select PARSENAME(-1,1)
Result: -1 --> Should not return integer part
结果:-1——>不应该返回整数部分。
select PARSENAME(0,1)
Result: 0
结果:0
select PARSENAME(1,1)
Result: 1 --> Should not return integer part
结果:1——>不返回整数部分
select PARSENAME(100.00,1)
Result: 00
结果:00
Results as per 1st approach above by Pavel Morshenyuk "0." is part of result in this case.
根据上面帕维尔·莫申约克“0”的第1种方法得出的结果是这种情况下的结果的一部分。
SELECT (100.0001 % 1)
Result: 0.0001
结果:0.0001
SELECT (100.0010 % 1)
Result: 0.0010
结果:0.0010
SELECT (0.0001 % 1)
Result: 0.0001
结果:0.0001
SELECT (0001 % 1)
Result: 0
结果:0
SELECT (1 % 1)
Result: 0
结果:0
SELECT (100 % 1)
Result: 0
结果:0
Combining both:
结合这两个:
SELECT PARSENAME((100.0001 % 1),1)
Result: 0001
结果:0001
SELECT PARSENAME((100.0010 % 1),1)
Result: 0010
结果:0010
SELECT PARSENAME((0.0001 % 1),1)
Result: 0001
结果:0001
SELECT PARSENAME((0001 % 1),1)
Result: 0
结果:0
SELECT PARSENAME((1 % 1),1)
Result: 0
结果:0
SELECT PARSENAME((100 % 1),1)
Result: 0
结果:0
But still one issue which remains is the zero after the non zero numbers are part of the result (Example: 0.0010 -> 0010). May be one have to apply some other logic to remove that.
但是仍然有一个问题仍然存在,那就是非零数之后的0是结果的一部分(示例:0.0010 -> 0010)。也许你必须运用一些其他的逻辑来去除它。
#6
0
If you know that you want the values to the thousandths, place, it's
如果你想要的值是千分位
SELECT (num - FLOOR(num)) * 1000 FROM table...;
#7
0
Make it very simple by query:
通过查询使其非常简单:
select substr('123.123',instr('123.123','.')+1, length('123.123')) from dual;
Put your number or column name instead 123.122
把你的号码或列名改为123.122
#8
0
If you want to select only decimal numbers use this WHERE clause:
如果你想只选择十进制数字使用这个WHERE子句:
(CAST(RIGHT(Myfield, LEN( Myfield)-CHARINDEX('.',Myfield)+1 ) AS FLOAT)) <> 0
If you want a clear list you can sort by decimal/integer:
如果你想要一个清晰的列表,你可以按十进制/整数排序:
CASE WHEN 0 = CAST(RIGHT(Myfield, LEN( Myfield)-CHARINDEX('.',Myfield)+1 ) AS FLOAT) THEN 'Integer' ELSE 'Decimal' END AS Type
#9
-1
You can use RIGHT :
你可以使用权利:
select RIGHT(123.45,2) return => 45
#10
-1
CAST(RIGHT(MyField, LEN( MyField)-CHARINDEX('.',MyField)+1 ) AS FLOAT)
将(右(MyField, LEN(MyField)-CHARINDEX('.',MyField)+1)转换为FLOAT)