Post reply

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

  • February 17, 2020 at 5:28 pm

    #3726525

    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!

  • February 17, 2020 at 5:58 pm

    #3726531

    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

    #3726539

    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

    #3726549

    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

    #3729313

    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