February 26, 2019 at 6:54 pm
Dear Forum,
I have a strange requirement to round a value in a varchar column in MS SQL*Server 2012. I know it is bad practice to hold numerics in a varchar.
i.e. the column is a varchar but holds a string representing a number.
I have string values like 834.78330000000005 and 831.06669999999997 and 797.45000000000005 but I want to update these to string values like 834.7833 and 831.0667 and 797.45 (trimming the trailing zeros not too important but desirable).
This seems to be close, are there better options? Should I use round function? :
CREATE TABLE [Clinical].[AAAJFJunk](
[Call Length] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT [Clinical].[AAAJFJunk] ([Call Length] ) VALUES (N'834.78330000000005')
INSERT [Clinical].[AAAJFJunk] ([Call Length]) VALUES (N'831.06669999999997')
INSERT [Clinical].[AAAJFJunk] ([Call Length]) VALUES (N'797.45000000000005')
GO
update Clinical.AAAJFJunk
set [Call Length] =
cast(cast([Call Length] as decimal(11,4)) as varchar)
;
February 26, 2019 at 6:59 pm
Since you can only round numbers, you'd have to cast the values as a decimal and round those. You can't round a string.
February 27, 2019 at 3:46 am
Out of interest, why are you storing decimal values in a varchar? Doing things like that are only going to come back to bite you. For example, in your data, the value '9' is greater than the value '831.06669999999997'. I'd strongly recommend looking at fixing your datatype.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 28, 2019 at 9:34 pm
allan.ford17 - Tuesday, February 26, 2019 6:54 PMI agree that it is bad, bad, bad to hold numeric values in varchar ... but sometimes these things happen in data processing, data quality checking ... when data source is a CSV file sent ..
Thank you for the replies and comments.I also discussed this same question on stackoverflow forums area:
Please see this discussion also.
A solution is perhaps as per this one (for my specific requirements):
UPDATE Clinical.AAAJFJunk
SET [Call Length] =
case when ISNULL([Call Length], '') = ''
then ''
else
FORMAT(Cast ( CONVERT(numeric(16,4), CAST([Call Length] AS FLOAT)) as float),'########0.####')
end
select
case when ISNULL([Call Length], '') = '' then ''
else FORMAT(Cast ( CONVERT(numeric(16,4), CAST([Call Length] AS FLOAT)) as float),'########0.####')
end as val123
from Clinical.AAAJFJunk
March 11, 2019 at 1:09 am
We can convert to float, it will take care.
Select TRY_CONVERT(FLOAT,[Call Length]) FROM AAAJFJunk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply