none
isnumeric/isdate in a case statement doesnt do what i thought it would

    Question

  • this is driving me crazy..

    i have a table, the table contains a list of varchar values. Sometimes these can be converted into ints or datetimes. I know to do this because of a field called 'property' that tells me its a date or an int.

    Data entry folks can make mistakes. i need to check the values of the entry and if correct allow the field to be used for updating another table, if not, set the value to NULL so that the invalid entry wont cause an error.

    The query below returns NULL if the contents of the field are numeric or date and the type indicates they should be.

    Any help here is greatly appreciated.

    here is the query.

    select
    ,value
    ,property

    ,case when property in (2304,2306,2302) and isdate(value) <> 1 then NULL
     when property in (2300) and isnumeric(value) <> 1 then NULL
    else value
    end convertable_value

    from base_table 




    • Edited by tolnep Thursday, August 30, 2012 2:59 PM
    Thursday, August 30, 2012 2:56 PM

Answers

  • It is definitely odd.  I can run your query (below with my test data) and it returns as expected.  Are you sure you don't have any extra hidden characters in your data?  What happens if you add a DATALENGTH column?

    Declare @tvTable Table (
    	property		int
    	,value	varchar(20)
    )
    
    Insert @tvTable
    Select 2300, '12345'
    Union All
    Select 2304, '20120801'
    Union All
    Select 2306, '10-APR-2012'
    Union All
    Select 2302, '12/31/2012'
    
    select 
    value
    ,DATALENGTH(value) Data_Length
    ,property
    ,case when property in (2304,2306,2302) and isdate(value) <> 1 then NULL
     when property in (2300) and isnumeric(value) <> 1 then NULL
    else value 
    end convertable_value 
    
    from @tvTable;


    Thursday, August 30, 2012 3:35 PM
  • >> I have a table, the table contains a list of VARCHAR(n) values. Sometimes these can be converted into INTEGERs or DATETIMEs. I know to do this because of a field called 'property' that tells me its a DATEi or an INTEGERs. <<

    Can you kill the moron that did this to you? This is not a table; look up First Normal Form (1NF). Columns are not fields and one of the many, many ways that a column with its data type is not a column. 

    >> Data entry folks can make mistakes. I need to check the values of the entry and if correct allow the field [sic] to be used for updating another table, if not, set the value to NULL so that the invalid entry wont cause an error. <<

    No, data entry should catch entry errors, not the database. A tiered architecture has presentation layers and data source errors. We do not format display data in the database; they do not pass us garbage. The design of your application is fundamentally wrong. You have thrown out the 40+ years of IT. 

    The isdate() and isnumeric() are what we call “kludge tools” that have to deal with all possible numerics (float, real, integer, etc.) and all possible date display formats. Use a regular expression in a LIKE predicate instead for a quick filter, if the front guys fail you. 
     
     crap_int LIKE '[ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][0-9]'

    Which will allow leading blanks. Then allow only the ISO-8601 date format with this quick filter. 

     crap_date LIKE '[12][0-9][0-9][0-9]-[01][0-9]-[03][0-9]' 

    Then you can do a try and catch on “CAST(crap_date AS DATE)”

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, August 31, 2012 6:35 PM

All replies

  • What is the problem, and how can we reproduce it?


    AMB

    Some guidelines for posting questions...

    Thursday, August 30, 2012 3:10 PM
  • What is the problem, and how can we reproduce it?


    AMB

    Some guidelines for posting questions...

    insert into another_table(some_value,some_property,groomed_value)

    select
    ,value
    ,property

    ,case when property in (2304,2306,2302) and isdate(value) <> 1 then NULL
     when property in (2300) and isnumeric(value) <> 1 then NULL
    else value
    end convertable_value

    from base_table;

    will end up with NULLS inserted for 'groomed_value' where the property is either numeric or date and the contents of 'value' field in the select from table is valid for the indicated type.

     
    in fact just running the select always returns 'NULL' for those fields as described.
    • Edited by tolnep Thursday, August 30, 2012 3:18 PM
    Thursday, August 30, 2012 3:18 PM
  • IsNumeric does not always behave as expected, and I suspect that IsDate is the same way.  What values is it reporting incorrectly?

    Also, for additional reading, check this post (of particular interest is johnqflorida's response, which is the 2nd to last response in the thread):

    http://social.msdn.microsoft.com/Forums/en-BZ/transactsql/thread/ae6f5f5d-3249-46b8-9431-87f95614be9b

    Thursday, August 30, 2012 3:18 PM
  • Try it like this...

    CASE
    	WHEN (property in (2304,2306,2302) and ISDATE(value) = 0)
    	OR
    	(property = 2300 and ISNUMERIC(value) = 0)
    	THEN NULL
    	ELSE value 
    END convertable_value 


    Jason Long

    Thursday, August 30, 2012 3:24 PM
  • IsNumeric does not always behave as expected, and I suspect that IsDate is the same way.  What values is it reporting incorrectly?

    Also, for additional reading, check this post (of particular interest is johnqflorida's response, which is the 2nd to last response in the thread):

    http://social.msdn.microsoft.com/Forums/en-BZ/transactsql/thread/ae6f5f5d-3249-46b8-9431-87f95614be9b

    im familiar with those types of issues.

    as an exact example.

    one field who's property indicates its a date contains '10-APR-2012' in a varchar field.

    another contains '12345' in the varchar field and its property indicates it should be an int.

    the case statement as above returns null for these two examples.

    this is not an instance of having floating or decimal or hexadecimal characters. The case statement simply fails. Which is really odd.

    Thursday, August 30, 2012 3:24 PM
  • It is definitely odd.  I can run your query (below with my test data) and it returns as expected.  Are you sure you don't have any extra hidden characters in your data?  What happens if you add a DATALENGTH column?

    Declare @tvTable Table (
    	property		int
    	,value	varchar(20)
    )
    
    Insert @tvTable
    Select 2300, '12345'
    Union All
    Select 2304, '20120801'
    Union All
    Select 2306, '10-APR-2012'
    Union All
    Select 2302, '12/31/2012'
    
    select 
    value
    ,DATALENGTH(value) Data_Length
    ,property
    ,case when property in (2304,2306,2302) and isdate(value) <> 1 then NULL
     when property in (2300) and isnumeric(value) <> 1 then NULL
    else value 
    end convertable_value 
    
    from @tvTable;


    Thursday, August 30, 2012 3:35 PM
  • >> I have a table, the table contains a list of VARCHAR(n) values. Sometimes these can be converted into INTEGERs or DATETIMEs. I know to do this because of a field called 'property' that tells me its a DATEi or an INTEGERs. <<

    Can you kill the moron that did this to you? This is not a table; look up First Normal Form (1NF). Columns are not fields and one of the many, many ways that a column with its data type is not a column. 

    >> Data entry folks can make mistakes. I need to check the values of the entry and if correct allow the field [sic] to be used for updating another table, if not, set the value to NULL so that the invalid entry wont cause an error. <<

    No, data entry should catch entry errors, not the database. A tiered architecture has presentation layers and data source errors. We do not format display data in the database; they do not pass us garbage. The design of your application is fundamentally wrong. You have thrown out the 40+ years of IT. 

    The isdate() and isnumeric() are what we call “kludge tools” that have to deal with all possible numerics (float, real, integer, etc.) and all possible date display formats. Use a regular expression in a LIKE predicate instead for a quick filter, if the front guys fail you. 
     
     crap_int LIKE '[ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][0-9]'

    Which will allow leading blanks. Then allow only the ISO-8601 date format with this quick filter. 

     crap_date LIKE '[12][0-9][0-9][0-9]-[01][0-9]-[03][0-9]' 

    Then you can do a try and catch on “CAST(crap_date AS DATE)”

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, August 31, 2012 6:35 PM