SQL Round if numerical?

Question

(Beginner at sql)

I've been getting the error

'Error converting data type nvarchar to float.'

Which is because I was trying to round an nvarchar(10) column with both characters and integers, and obviously it can't round the characters. (I can't make two separate columns with different data types as they both need to be in this column)

I'm looking for a way to round the numbers in the nvarchar column whilst also returning the characters

I've being trying CAST/Converts nothing seems to work

I've also tried

CASE WHEN ISNUMERIC(Tbl1.Column1) = 1
THEN    cast(Round(Tbl1.Column1, 0) AS float)
ELSE Tbl1.Column1 END   AS 'Column1'

in the select statement

I cant figure out what else will solve this!

Sample Data in this column would be

 8.1
 2
 9.0
 9.6
 A
 -
 5.3
 D
 E
 5.1
 -

Answer 1

since you hold both types in this column, you need to cast your rounded value back to varchar

declare @Tbl1 table (Column1 varchar(10))

insert into @Tbl1 (Column1) values ('8.1'), ('2'), ('9.0'), 
                                   ('9.6'), ('A'), ('5.3'), 
                                   ('D'), ('E'), ('5.1'), ('-')

select case when TRY_CONVERT(float, Column1) IS NULL then Column1
            else cast(cast(Round(Column1, 0) as float) as varchar(10))
       end  AS 'Column1' 
from   @Tbl1      

outcome is

Column1
-------
8
2   
9
10  
A   
5   
D   
E   
5   
-

In case you get the error TRY_CONVERTis not a build-in function then you have your database compatibility level is less that SQL 2012.

You can correct that using this command

ALTER DATABASE your_database SET COMPATIBILITY_LEVEL = 120;

Also note that after this statement the answer of Gordon is working now, and I agree that is a better answer then mine

Answer 2

I would go for try_convert() instead of isnumeric():

COALESCE(CONVERT(VARCHAR(255), TRY_CONVERT(DECIMAL(10, 0), Tbl1.Column1)),Tbl1.Column1) as Column1

A conversion problem arises with your approach because a case expression returns a single value. One of the branches is numeric, so the return type is numeric -- and the conversion in the else fails.

You can fix your version by converting the then clause to a string after converting to a float.