Tuesday, 6 October 2015

Handling decimal and non numeric types using Case statement

Hi Guys

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



Requirement :- You need to round off the decimal values to 2 places 


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

6 comments:

  1. Hi Lokesh,
    I concur that your solution will "work" but one of the tenets of relational theory is that the data in a column is all of the same type. I understand that in ETL processes that is often not true (the raw data is what it is ;( ) but once we get it in SQL we should try to enforce the basic relational concepts.

    ReplyDelete
  2. I agree with Ray, but I will go further. We need to beat up the moron who did this to us and fire him! Skin him and make a Cellphone jacket from him!

    That is probably illegal, so let's put a regular expression on the VARCHAR(n), NVARCHAR(n), CHAR(n) and NCHAR(n) columns instead :)

    ReplyDelete
  3. You are assuming that anything with a '.' is numeric. If you have a value like 'James T. Kirk', casting to decimal will fail. Might want to add an isnumeric() predicate to the case.

    ReplyDelete
  4. Thanks @ray, @celko and @unknown. This is the beauty of putting up things on blog, you get new suggestions.

    @unknown - you are absolutely right. Isnumeric would be a better. Above one is just a dummy example. In my case we had additional column associated which is just a logical heading of the values column and columns which had floating values would always come under one logical heading. So can get away simply by adding a where clause on this column.

    ReplyDelete
  5. I'm wondering if this is more useful this case.
    SELECT UpdatedValue
    , CASE WHEN UpdatedValue LIKE '%[0-9].[0-9]%' THEN CAST(CONVERT(DECIMAL(18,2), UpdatedValue) AS VARCHAR(100))
    ELSE UpdatedValue
    END
    FROM #TEST
    Since
    1) CHARINDEX can't filter 'John A. Banks'
    2) ISNUMERIC returns all numeric values including the ones that don't need rounding like 34.

    ReplyDelete
  6. I'm wondering if this is more useful this case.
    SELECT UpdatedValue
    , CASE WHEN UpdatedValue LIKE '%[0-9].[0-9]%' THEN CAST(CONVERT(DECIMAL(18,2), UpdatedValue) AS VARCHAR(100))
    ELSE UpdatedValue
    END
    FROM #TEST
    Since
    1) CHARINDEX can't filter 'John A. Banks'
    2) ISNUMERIC returns all numeric values including the ones that don't need rounding like 34.

    ReplyDelete

SSIS Issues : A Day of Learning

Seldom are the days when you run into complex issues but resolve them in the shortest interval of time. Thanks to Larry Page and my fello...