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

Issue

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

Solution

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

Answered By - Larnu

Answer Checked By - Cary Denson (PHPFixing Admin)