0

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;

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        
  • the datatypes are automatically allocated in azure and the number/value mix is as per the dataset (different codes as per the diagnosis). Thinking about it i might load the data into sql and then import to azure as i would have more control over the data types. – clor Jun 26 at 9:50
  • thank you so much – clor Jun 26 at 10:13
  • The select diag_1 TRY_CONVERT is good but unfortunately i'm getting an error with the DECLARE, INSERT, with x AS. – clor Jun 26 at 19:54
  • @clor what error? – Aaron Bertrand Jun 26 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.