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

Comments

  1. Anonymous14 October 2015 at 09:28

    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. --CELKO--14 October 2015 at 10:46

    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. Unknown14 October 2015 at 16:34

    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. Lokesh14 October 2015 at 19:53

    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. killingbee17 December 2015 at 14:45

    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. killingbee17 December 2015 at 14:47

    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

Post a Comment

Popular posts from this blog

Issues Integrating Azure Data Factory with GITHUB | IN spite of admin rights on repository

-
Image
Although I have been aware of GitHub for quite a while but got a chance to use it as a repository in the project for storing artifacts only over the past few months.  We use GitHub as the sole cloud repo for storing SQL, Databricks, and Azure Data Factory artifacts.  Got an opportunity to set up GitHub with ADF right from scratch and all the excitement to learn more about GitHub took a back seat when I stumbled upon the following issue Problem Statement - You are an admin on a repo in GitHub and even then get a permission error while linking the ADF to GitHub Solution - After scratching my hairs and going on a searching spree on google for some days, I was able to figure out what needed to be done, however was not aware of how to implement it. Finally thanks to Microsoft for getting on a call and helping me resolve this issue. What needed to be done - Need to authorize AzureDataFactory service so that Github can communicate with it.  How it should be done -  Basically, you need to auth
Read more

SQL QUERY NIGHTMARE

-
Image
Last week, we faced a basic and yet the only major problem that sql developers/admin had to deal with and yes your guess is right, It was with SQL QUERY RUNNING SLOW.  I will be explaining the problem below, however I want everyone reading this to help me find answers because I still haven't found one! One more amazing aspect of the issue was that it worked fairly quickly in Development but was running like a tortoise in Production even though the number of records were practically the same. I'll start by throwing some light on to the execution plan, so that we narrow down to the code which was the culprit here. The code is enclosed into a stored proc. On contacting my dba to enquire about the issue, I got the following replay:- I must hand it to our dba to provide such a detailed response.  So my job was now to look at the code and modify it. What I did was to shrink the calender table into a temp table consisting of few rows that would speed up the operation. Bel
Read more