我的表格資料
| 金額1 | 金額2 | 預期結果 |
|---|---|---|
| 100 | 200 | 4.17% |
| 一種 | 500 | |
| 500 | 乙 | |
| 20 | 100 | 1.67% |
CREATE TABLE [dbo].[tblData](
[Amount1] [nvarchar](50) NULL,
[Amount2] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT [dbo].[tblData] ([Amount1], [Amount2]) VALUES (N'100', N'200')
INSERT [dbo].[tblData] ([Amount1], [Amount2]) VALUES (N'A', N'500')
INSERT [dbo].[tblData] ([Amount1], [Amount2]) VALUES (N'', N'')
INSERT [dbo].[tblData] ([Amount1], [Amount2]) VALUES (N'500', N'B')
INSERT [dbo].[tblData] ([Amount1], [Amount2]) VALUES (N'20', N'100')
當我在一行上運行它時，這可以正常作業
SELECT TOP 1
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%') AS Result
FROM tblData
當我在整個桌子上運行它時失敗了
SELECT
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%') AS Result
FROM tblData
回傳以下錯誤訊息。
將資料型別 nvarchar 轉換為數字時出錯
uj5u.com熱心網友回復：
如果您有一個主要是數字的文本欄位，但您不能依賴 then 而不是
cast(Amount1 AS decimal(18,4))
你可以使用
try_cast(Amount1 AS decimal(18,4))
如果無法完成轉換，則回傳 Null
我使用 try_convert 但這只是個人喜好，它們實際上是相同的
try_convert（十進制（18,4），金額1）
所以你的陳述的開頭看起來像這樣
case
when try_cast(Amount1 as decimal(18,4)) is not null --Numeric
and try_cast(Amount2 as decimal(18,4)) > 0 --Numeric and not zero
then Concat(CAST((try_cast(Amount1 AS decimal(18,4))/(try_cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%')
uj5u.com熱心網友回復：
在執行任何操作之前檢查列是否為數字：
SELECT Amount1
, Amount2
, case when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1 then
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%')
else
null
end AS "Expected Result"
FROM tblData
這是一個演示
如果您的資料中有 0，則如下所示：
SELECT Amount1, Amount2,
case when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1 and Amount2 > 0 and Amount1 > 0 then
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%')
when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1 and Amount1 = 0 and Amount1 > 0 then
Concat(CAST((cast(1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%')
when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1 and Amount1 > 0 and Amount1 = 0 then
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(1 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%')
else
null
end AS "Expected Result"
FROM tblData
這是第二個演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/456567.html
上一篇：如何在SQLServer連接陳述句的右選擇陳述句中的where子句中使用左表中的列
下一篇：根據型別獲取記錄Z-Score