Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, August 30, 2012 2:56 PM
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
Thursday, August 30, 2012 3:35 PM ✅Answered | 1 vote
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;
Friday, August 31, 2012 6:35 PM ✅Answered | 1 vote
>> 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
Thursday, August 30, 2012 3:10 PM
What is the problem, and how can we reproduce it?
AMB
Some guidelines for posting questions...
Thursday, August 30, 2012 3:18 PM
What is the problem, and how can we reproduce it?
AMB
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.
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):
Thursday, August 30, 2012 3:24 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):
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.