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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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.
SELECTMyColumn,
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy