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为十进制（18,3））/ 1000 不分值

它给分吧我更多个零小数点后代替。 （我甚至括在括号和尝试，但结果相同）。

It gives me more number of zeros after decimal instead of dividing it. (I even enclosed that in brackets and tried but same result).

Ex:2000/1000 = 2000.000000

我是不是做错了什么吗？是PATINDEX EX pression参数是否正确？

Am I doing something wrong here? Are patindex expression parameters correct?

Expected result:
4.000
3.123
7.000
80.000

不要让我知道如果PATINDEX不使用正确的方法？ 我试图检查其是否已经与3小数点一样，我希望通过1000分十进制。

Do let me know if PATINDEX is not correct method to use? I'm trying to check if it is already decimal with 3 decimal points else i want to divide by 1000.

推荐答案

试试这个..

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位小数输出做到这一点

Edit - to keep to 3 decimal places in output do this

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）定义了这一点，总precision 8位，3点后。

Note the (8,3) defines this, total precision 8 digits, 3 after the point.

您可能希望将其转换回为varchar（8）太

You may wish to convert back to varchar(8) too