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)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.