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

  • A+
Category:Languages

I am using MS SQL Server 2014 and have a table with 3 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 2 digits. For example, I want 123456 to become 12.3456. The numbers are different lengths; some are 5 digits, some are 7 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 

 


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

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: