如何在 SQL 中将 nvarchar 转换为十进制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42527953/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert nvarchar to decimal in SQL
提问by Arun D
How to convert an nvarchar to decimal in SQL Server?
如何在 SQL Server 中将 nvarchar 转换为十进制？
I need to convert a value like '38,716.1311'to 38716.1311.
我需要将一个值转换'38,716.1311'为 38716.1311。
I have tried using this method but can't successfully convert:
我曾尝试使用此方法但无法成功转换：
DECLARE @TempValue NVARCHAR(100) = '38,716.1311'
SELECT
CONVERT(DECIMAL(18, 2), @TempValue)
Can anyone suggest the correct query? Thanks in advance
谁能建议正确的查询？提前致谢
回答by Andy Korneyev
Just remove comma from your string prior to conversion:
只需在转换前从字符串中删除逗号：
Select CONVERT(DECIMAL(18,2),replace('38,716.1311', ',', ''))
回答by rjps12
Its better if the @TempValueis MONEYso it disregard the comma separated
如果@TempValue是MONEY这样它会更好地忽略逗号分隔
Declare @TempValue MONEY = '38,716.1311'
Select CAST(@TempValue AS DECIMAL(18,2))
回答by Singh Kailash
Declare @TempValue nvarchar(100)
SET @TempValue = Replace('38,716.1311',',','')
Select cast(@TempValue AS DECIMAL(18,2))
--we can not convert value Like "38,716.1311" (NVARCHAR) To DECIMAL so first we replace "," with blank "38716.1311" then convert with DECIMAL
-- 我们不能将像 "38,716.1311" (NVARCHAR) 这样的值转换为 DECIMAL 所以首先我们用空白 "38716.1311" 替换 "," 然后用 DECIMAL 转换
回答by Mansoor
Use MONEY data type and convert into decimal data type:
使用 MONEY 数据类型并转换为十进制数据类型：
DECLARE @Col MONEY = '38,716.1311'
SELECT CONVERT(DECIMAL(20,4),@Col)
OR Replace comma and convert into decimal
或替换逗号并转换为十进制
DECLARE @Col VARCHAR(100) = '38,716.1311'
SELECT CONVERT(DECIMAL(20,4),REPLACE(@Col,',',''))
回答by Rob
Remove the comma by using REPLACE like so:
使用 REPLACE 删除逗号，如下所示：
Declare @TempValue nvarchar = '38,716.1311'
Select CONVERT(DECIMAL(18,2), REPLACE(@TempValue, ',', ''))
回答by Gerardo F
In my case for example some data like: version: '1.2.12'
就我而言，例如一些数据，例如： version: '1.2.12'
I want to register like version: 1.212 in Sql
我想在Sql中注册类似版本：1.212
Clean the varchar( eliminate dot or commas )
Cast to char(6 digit) and pad with 0 the empty spaces. (Catch null with coalesce)
Cast to numericand divide to 100000
Cast to decimal(10,4)
清理varchar（消除点或逗号）
转换为字符（6 位）并用 0 填充空格。（使用合并捕获空值）
转换为数字并除以 100000
转换为十进制(10,4)
Result ->> version 1.212
结果 ->> 版本 1.212
Select 'version'=cast(cast(replace(cast(replace(coalesce([##DATA##],'0'), '.', '') as char(6)),' ','0')as numeric(6))/100000 as decimal (10,4)
Maybe there is another solution which is more simple.
也许还有另一种更简单的解决方案。
