convert string to integer and round

Question

I have the below select stmts but I get an error message when i try to combine them. I want to convert diag_1 varchar datatype for example 250.8 to an integer and then round down to the nearest full number eg 250.

Also the data contains values with V25 aswell as numeric. Is it possible to remove the 'V' from some of the data?

Select encounter_id,diag_1,diag_2,diag_3, Try_convert(float,[diag_1]) as [float1], Try_convert(float,[diag_2]) as [float2], Try_convert(float,[diag_3]) as [float3] from dbo.encounters;

Select round(float1,1) as roundvalue from dbo.encounters;

Answer 1

Lots going on here. Why are you storing numbers in a varchar column? Why do you sometimes have a number that's only kind of a number? You can fix some of these complications of course by cleaning the data before inserting it and using the right data type. In the meantime, something like this is ugly but gets the job done.

DECLARE @encounters table(diag_1 varchar(50));

INSERT @encounters(diag_1) VALUES('240'),('250.8'),('V25'),('V26.2'),('Vurt'),('V303V2');

;WITH x AS 
(
  SELECT diag_1, 
    adj = CASE -- first, remove the leading V where it exists:
      WHEN diag_1 LIKE 'V%' THEN SUBSTRING(diag_1,2,255)
      ELSE diag_1 END
  FROM @encounters
)
SELECT diag_1, -- now, try to cast to int the remainder, ending right before the decimal
  adjusted = TRY_CONVERT(int,LEFT(adj, COALESCE(NULLIF(CHARINDEX('.',adj)-1,-1),255)))
FROM x;

Results:

diag_1   adjusted
------   --------
240           240
250.8         250
V25            25
V26.2          26
Vurt         NULL  
V303V2       NULL