Just encountered a scenario where I needed to handle numeric and non numeric types using Case statement. Here is the synopsis of challenge and solution:-

Challenge :- You have a varchar column in your input table having values such as 1.1111, 34, John.

CREATE TABLE #TEST ([UpdatedValue] VARCHAR(100))

INSERT INTO  #TEST VALUES('1.1111'),('34'),('John')

select * from #TEST

Solution :- I got to know while implementing that for a case statement to work the output data i.e. the resultant data from each case should be of same type. It won't be able to treat numeric and non numerics together. That is why I personally found it a bit tricky but on brain storming solution looked to be very simple. The basic idea is to use different select queries and then union together. Here it is :-

select CASE WHEN CHARINDEX('.',[UpdatedValue],0) >=1 THEN CAST( CAST ([UpdatedValue] AS DECIMAL(18,2)) AS VARCHAR(21))

ELSE [UpdatedValue] END AS [UpdatedValue]

 FROM #TEST WHERE [UpdatedValue] !='John'

 UNION

select *

 FROM #TEST WHERE ISNUMERIC( [UpdatedValue])=0