SQL Server - CAST和DIVIDE

Question

DECLARE @table table(XYZ VARCHAR(8) , id int)

INSERT INTO @table
SELECT '4000', 1
UNION ALL
SELECT '3.123', 2
UNION ALL
SELECT '7.0', 3
UNION ALL
SELECT '80000', 4
UNION ALL
SELECT NULL, 5

查询：

SELECT CASE 
         WHEN PATINDEX('^[0-9]{1,5}[\.][0-9]{1,3}$', XYZ) = 0 THEN XYZ
         WHEN PATINDEX('^[0-9]{1,8}$',XYZ) = 0 THEN CAST(XYZ AS decimal(18,3))/1000
         ELSE NULL 
       END
  FROM @table

这部分- CAST(XYZ AS decimal(18,3))/1000没有除值。

它给了我更多的小数后的零数，而不是除以它。(我甚至将其括在括号中，并尝试了相同的结果)。

Ex:2000/1000 = 2000.000000

我是不是做错了什么？patindex表达式参数是否正确？

Expected result:
4.000
3.123
7.000
80.000

如果PATINDEX不是正确的使用方法，请告诉我？我试着检查它是否已经是带有3个小数点的小数，否则我想除以1000。

Answer 1

试试这个..。

DECLARE @table table(XYZ VARCHAR(8) , id int)

INSERT INTO @table
SELECT '4000', 1
UNION ALL
SELECT '3.123', 2
UNION ALL
SELECT '7.0', 3
UNION ALL
SELECT '80000', 4
UNION ALL
SELECT NULL, 5
UNION ALL
SELECT 'WTF',6

SELECT CASE 
     WHEN ISNUMERIC(XYZ) = 0 THEN NULL
     WHEN CHARINDEX('.',XYZ,0) < LEN(XYZ)-2 AND CHARINDEX('.',XYZ,0) > 0 THEN XYZ
     WHEN ISNUMERIC(XYZ) >0  then  convert(decimal(18,3),xyz) / 1000.000
     ELSE NULL
END
  FROM @table

输出

4.00000000000
3.12300000000
0.00700000000
80.00000000000
NULL
NULL

编辑-要在输出中保留3位小数，请执行以下操作

SELECT convert(decimal(8,3),CASE 
     WHEN ISNUMERIC(XYZ) = 0 THEN NULL
     WHEN CHARINDEX('.',XYZ,0) < LEN(XYZ)-2 AND CHARINDEX('.',XYZ,0) > 0 THEN XYZ
     WHEN ISNUMERIC(XYZ) >0  then  convert(decimal(18,3),xyz) / 1000.000
     ELSE NULL
END)
  FROM @table

请注意，(8,3)定义了这一点，总精度为8位，点后为3位。

您可能也希望转换回varchar(8)

Answer 2

尝试此操作，将整个大小写转换为十进制(18，3)：

DECLARE @table table(XYZ VARCHAR(8) , id int)

INSERT INTO @table
SELECT '4000', 1
UNION ALL
SELECT '3.123', 2
UNION ALL
SELECT '7.0', 3
UNION ALL
SELECT '80000', 4
UNION ALL
SELECT NULL, 5


SELECT CONVERT(decimal(18,3),CASE --nothing changes within the CASE
                                 WHEN PATINDEX('^[0-9]{1,5}[\.][0-9]{1,3}$', XYZ) = 0 THEN XYZ
                                 WHEN PATINDEX('^[0-9]{1,8}$',XYZ) = 0 THEN CAST(XYZ AS decimal(18,3))/1000
                                 ELSE NULL 
                             END
              )
FROM @table

输出：

---------------------------------------
4000.000
3.123
7.000
80000.000
NULL

(5 row(s) affected)

Answer 3

除以1000会得到小数点后的更多零

5.3 / 1000 = .0053