How can I update a table to insert decimal points at a fixed position in numbers?

Question

I am using Microsoft SQL Server 2014 and have a table with three columns and the field data type is Decimal(38,0).

I want to update each row of my table to insert a decimal point after the first two digits. For example, I want 123456 to become 12.3456. The numbers are different lengths; some are five digits, some are seven digits, etc.

My table is:

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

And I want to change this to:

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

My code is:

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

And I tried this:

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

Aurangzeb

Pretty sure you posted this question the other day. my point about your datatype in the previous question remains unchanged though, you're using a decimal(38,0); that's a precision of 0; it can't store a value with any decimal points. Like i mentioned before (as well) your data seems far better suited to something like a decimal(6,4). – Statics

Also, as I commented in Gordon's answer, pretty sure one of the problems was that you also had negative numbers in your sample data, however, you haven't included that in your samples (again). Things like STUFF will count - as a character, meaning that it isn't "always" the third character that needs to be the decimal point. – Statics

Why change it in the database instead of just rendering it on the way out? – Happening

This is an odd thing to be doing to numbers. You are asking for a transformation that is tied to their string representation, rather than their numerical value, which raises the question of whether the data truly is, or was cast at some point as, strings. – Selfacting

Answer 1

This runs on an assumption from a previously deleted post (that you have negative number as well).

Firstly, as you're using a decimal(38,0) you can't store values with any kind of precision, thus you need to change the data type as well. This provides the results you appear to be looking for:

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;

Note that you won't get a value like 41.45, but instead 41.4500. If you don't want to display trailing 0's you need to do the formatting in your presentation layer (otherwise you'd have to store the values as a varchar, and that's a very bad idea).

Statics

Answer 2

Try the following update:

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);

The logic here is to divide each number by 10 to the power of the number's length minus 2. This works for both positive and negative numbers, because we use the number's absolute value for normalizing. Follow the link below for a running demo.

Demo

Nanji

Answer 3

Just do this in the update:

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

The values are converted to strings, but the strings will be implicitly converted back to whatever type X and Y are. You may get an error if the types are not compatible.

If you have negative values, then you should include them in the same data. This is handled using 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, '.');

Cookshop

Answer 4

You can get the number of digits in a number using FLOOR(LOG10(num) + 1) and POWER(10, num_digits) to determine the number to divide with. No string operations at all:

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

You can easily extend it to handle values that have decimal portion:

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

Output:

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

Demo on db<>fiddle

Gonta