sql - 如何更新表格以在数字的固定位置插入小数点？

Question

我使用的是 Microsoft SQL Server 2014，有一个包含三列的表，字段数据类型为 Decimal(38,0)。

我想更新表格的每一行，在前两位数字后插入小数点。例如，我希望 123456 变为 12.3456。数字的长度不同；有些是五位数字，有些是七位数字，等等。

我的 table 是:

+-------------+-------+-------+
| ID          |   X   |   Y   |
+-------------+-------+-------+
| 1200        | 321121| 345000|
| 1201        | 564777| 4145  |
| 1202        | 4567  | 121444|
| 1203        | 12747 | 789887|
| 1204        | 489899| 124778|
+-------------+-------+-------+

我想将其更改为:

+-------------+--------+--------+
| ID          |   X    |   Y    |
+-------------+--------+--------+
| 1200        | 32.1121| 34.5000|
| 1201        | 56.4777| 41.45  |
| 1202        | 45.67  | 12.1444|
| 1203        | 12.747 | 78.9887|
| 1204        | 48.9899| 12.4778|
+-------------+--------+--------+

我的代码是:

Update [dbo].[UTM]
     SET [X] = STUFF([X],3,0,'.')
         [Y] = STUFF([X],3,0,'.')

我尝试了这个:

BEGIN
DECLARE @COUNT1 int;
DECLARE @COUNT2 int;
DECLARE @TEMP_X VARCHAR(255);
DECLARE @TEMP_Y VARCHAR(255);
DECLARE @TEMP_main VARCHAR(255);

SELECT @COUNT1 = COUNT(*) FROM [UTM];
SET @COUNT2 = 0;

    WHILE(@COUNT2<@COUNT1)
    BEGIN
        SET @TEMP_main = (SELECT [id] from [UTM] order by [id] desc offset @COUNT2 rows fetch next 1 rows only);
        SET @TEMP_X = (SELECT [X] from [UTM] order by [id] desc offset @COUNT2 rows fetch next 1 rows only);
        SET @TEMP_Y = (SELECT [Y] from [UTM] order by [id] desc offset @COUNT2 rows fetch next 1 rows only);

        UPDATE [dbo].[UTM]
           SET [X] = CONVERT(decimal(38,0),STUFF(@TEMP_X,3,0,'.'))
              ,[Y] = CONVERT(decimal(38,0),STUFF(@TEMP_Y,3,0,'.'))
           WHERE [id] = @TEMP_main;

        SET @COUNT2 = @COUNT2  +  1
    END

END

Answer 1

这是基于之前删除的帖子的假设(您也有负数)。

首先，由于您使用的是 decimal(38,0)，您无法存储任何精度的值，因此您还需要更改数据类型。这提供了您似乎正在寻找的结果:

USE Sandbox;
GO

CREATE TABLE dbo.SampleTable (ID int,
                              X decimal(38,0),
                              Y decimal(38,0));
INSERT INTO dbo.SampleTable (ID,
                             X,
                             Y)
VALUES (1200,321121,345000), 
       (1201,564777,4145  ), 
       (1202,4567  ,121444), 
       (1203,12747 ,789887), 
       (1204,489899,124778),
       (1205,-32472,-27921);
GO
--Fix the datatype
ALTER TABLE dbo.SampleTable ALTER COLUMN X decimal(10,4); --Based on data provided, may need larger scale
ALTER TABLE dbo.SampleTable ALTER COLUMN Y decimal(10,4); --Based on data provided, may need larger scale
GO

--update the data
UPDATE dbo.SampleTable
SET X = STUFF(ABS(CONVERT(int,X)),3,0,'.') * CONVERT(decimal(10,4),CASE WHEN X < 0 THEN -1.0 ELSE 1.0 END),
    Y = STUFF(ABS(CONVERT(int,Y)),3,0,'.') * CONVERT(decimal(10,4),CASE WHEN Y < 0 THEN -1.0 ELSE 1.0 END);

SELECT *
FROM dbo.SampleTable;
GO

DROP TABLE dbo.SampleTable;

请注意，您不会得到像 41.45 这样的值，而是得到 41.4500。如果您不想显示尾随 0，则需要在表示层中进行格式化(否则您必须将值存储为 varchar，这是一个非常糟糕的主意)。