February 17, 2020 at 5:28 pm
Hello All,
I have a question in removing trailing zeroes of varchar(50) column datatype in SQL Server.
Like if 100.100 is the value I need to do it in three different formats like
And I shouldn't want these values to do any rounding or like do up/down the decimal values. If it is 100.150 if I use the ROUND function it will change the value to 100.20. So I don't this behavior just trimming the values without changing the actual value in varchar datatype.
Any leads are appreciated. Thanks for reading this post though!
Thanks, and have a great Monday!
February 17, 2020 at 5:58 pm
SELECT value AS original_value,
CAST(ROUND(value, 0, 1) AS int) AS value0,
CAST(ROUND(value, 2, 1) AS decimal(9, 2)) AS value2,
CAST(ROUND(value, 1, 1) AS decimal(9, 1)) AS value1
FROM ( VALUES(100.199) ) AS test_data(value)
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
February 17, 2020 at 6:15 pm
Thanks for the reply but unfortunately it is not working. When I executed the same query in the separate window its works great but while I replace my code with yours I am getting an error saying
Msg 245, Level 16, State 1, Line 26
Conversion failed when converting the varchar value ' (' to data type int.
Here is the code I am using
WHEN 'SCC' THEN CONCAT(' (', CAST(ROUND(Column(Varchar(50)), 0, 1) AS int), ' ', nestedTempTableAlerts.[Threshold_Value], ')')
Thanks for the help.
February 17, 2020 at 7:22 pm
WHEN 'SCC' THEN CONCAT(' (', CAST(ROUND([Column], 0, 1) AS int), ' ', nestedTempTableAlerts.[Threshold_Value], ')')
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
February 27, 2020 at 3:47 pm
Rather then convert to numbers, transform, and convert back, you could just select the portion of the string you want.
SELECT MyColumn,
NewColumn = SUBSTRING(MyColumn, 1, CHARINDEX('.', MyColumn) + 1)
/* Needed if negatives have parentheses */ --+ CASE WHEN MyColumn LIKE '%)%' THEN ')' ELSE '' END
FROM (VALUES
('100.100'),
('(100.100)')
) v (MyColumn)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply