How to remove decimal trailing zeroes of varchar(50) in SQL Server

  • chowdarydeepus@gmail.com

    Old Hand

    Points: 338

    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

    1. 100 (No decimal values at all)
    2. 100.10 (Two decimal values by removing one last value)
    3. 100.1 (One decimal value by removing two last values)

    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!

  • ScottPletcher

    SSC Guru

    Points: 98434

    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."

  • chowdarydeepus@gmail.com

    Old Hand

    Points: 338

    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.

  • ScottPletcher

    SSC Guru

    Points: 98434

    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."

  • fahey.jonathan

    Hall of Fame

    Points: 3566

    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