Sqlserver Number needs to be convert as integer

I have data as below,

Id    EmpName   Amt
101   AAA       100.0000
102   BBB       -

If Amt is number i need to display 100(without decimal point) or I need to display as it is.

Expected o/p,

Id    EmpName   Amt
101   AAA       100
102   BBB       -

I used below but getting exception,

select id, empname, (CASE WHEN amt NOT LIKE '%[^0-9]%' then amt else left(CONVERT(varchar(50), CAST(ROUND(amt,0) AS money), 1),len(CONVERT(varchar(50), CAST(ROUND(amt,0) AS money), 1)) - 3) end ) 
            as ff,

3 answers

  • answered 2018-07-11 05:37 Lukasz Szozda

    You could use TRY_CAST:

    SELECT id, empname, ROUND(TRY_CAST(Amt AS DECIMAL(18,4)),0) AS amt
    FROM tab

  • answered 2018-07-11 06:24 Ajay Gupta

    Try this

    Select id, empname, CASE WHEN ISNUMERIC(amt) = 1 AND amt NOT IN ('-','+','$','.',',','\') 
                             THEN CAST(Round(CAST(amt as float),0) as varchar(50)) 
                             ELSE amt END as amt 
    FROM tbl

  • answered 2018-07-11 11:49 Gordon Linoff

    I would use try_convert():

    select id, empname,
           (case when try_convert(decimal(18, 0), amt)
                 then convert(varchar(255), convert(decimal(18, 0), amt))
                 else amt
            end) as ff
    . . .