HOW can edit number in TABLE with UPDATE in MSSQL SERVER 2014

  • A+
Category:Languages

I have table with 3 column and field Data Type is Decimal(38,0). so I want to update each row of my table . Actually the length of number is not same as each other I mean some of them is 5 digit and some of them 7 digit but I want after 2 first digit insert '.' (Decimals) for example 123456 should be 12.3456

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 :

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

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

and I try 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: