Как я могу обновить таблицу, чтобы вставить десятичные точки в фиксированной позиции в числах?

Я использую MS SQL Server 2014 и имею таблицу с 3 столбцами, а тип данных поля - Decimal(38,0).

Я хочу обновить каждую строку моей таблицы, чтобы вставить десятичную точку после первых 2 цифр. Например, я хочу, чтобы 123456 стал 12.3456. Числа разной длины; некоторые из 5 цифр, некоторые из 7 цифр и т.д.

Мой стол:

+-------------+-------+-------+
| 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

Ответ 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, и это очень плохая идея).

Ответ 2

Просто сделайте это в update:

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

Значения преобразуются в строки, но строки будут неявно преобразованы обратно в любой тип X и Y Вы можете получить ошибку, если типы не совместимы.

Если у вас есть отрицательные значения, то вы должны включить их в те же данные. Это обрабатывается с помощью case:

Update [dbo].[UTM]
     SET X = STUFF(CONVERT(VARCHAR(255), X), (CASE WHEN X < 0 THEN 4 ELSE 3 END), 0, '.'),
         Y = STUFF(CONVERT(VARCHAR(255), X), (CASE WHEN X < 0 THEN 4 ELSE 3 END), 0, '.');

Ответ 3

Попробуйте следующее обновление:

UPDATE UTM
SET
    X = CAST(X AS DECIMAL(10,2)) / POWER(10, LEN(CAST(ABS(X) AS VARCHAR(10)))-2),
    Y = CAST(Y AS DECIMAL(10,2)) / POWER(10, LEN(CAST(ABS(Y) AS VARCHAR(10)))-2);

Логика здесь состоит в том, чтобы делить каждое число на 10 на степень длины числа минус 2. Это работает как для положительных, так и для отрицательных чисел, потому что мы используем абсолютное значение числа для нормализации. Перейдите по ссылке ниже для демонстрации.

демонстрация

Ответ 4

Вы можете получить количество цифр в номере, используя FLOOR(LOG10(num) + 1) и POWER(10, num_digits) чтобы определить число, на которое нужно разделить. Никаких строковых операций вообще:

DECLARE @t TABLE (ID INT, X DECIMAL(38, 0), Y DECIMAL(38, 0));
INSERT INTO @t VALUES
(1200, 321121, 345000),
(1201, 564777,   4145),
(1202,   4567, 121444),
(1203,  12747, 789887),
(1204, 489899, 124778);

SELECT ID
     , X, X / POWER(10, FLOOR(LOG10(ABS(X))) + 1 - 2) AS X2
     , Y, Y / POWER(10, FLOOR(LOG10(ABS(Y))) + 1 - 2) As Y2
FROM @t

Вы можете легко расширить его для обработки значений, которые имеют десятичную часть:

DECLARE @t TABLE (X DECIMAL(38, 8));
INSERT INTO @t VALUES
( 12345.00000),
( 12345.67890),
(-12345.00000),
(-12345.67890);

SELECT X, CASE
    WHEN XS >= 0 THEN X / POWER(10, XS)
    ELSE X * POWER(10, -XS)
END X2
FROM @t
CROSS APPLY (SELECT FLOOR(LOG10(ABS(X))) + 1 - 2 AS XS) AS CA

Выход:

| X               | X2         |
|-----------------|------------|
|  12345.00000000 |  12.345000 |
|  12345.67890000 |  12.345679 |
| -12345.00000000 | -12.345000 |
| -12345.67890000 | -12.345679 |

Демо на дб <> скрипка