September 27, 2013 at 9:33 am
Maybe I am a bit dense today since it is a Friday and I am looking towards the weekend.. But this isn't making sense to me.
I have a web form with a QTY in it, it has javascript to only allow numbers, but people disable their javascript and enter crazy things in qty fields sometimes..
SO... I am writing some code and getting the error.
I have simplified it down to this, but still get the error.
Doesn't make sense to me since I am using the ISNUMERIC to only apply the CAST function to records that are actually numbers.
select case when ISNUMERIC(QtyOrdered)=1
then CAST(QtyOrdered AS decimal(19,6))
else 0.0
end as Qty
from orders
Any ideas? Thanks in advance.
September 27, 2013 at 10:13 am
IsNumeric indicates if the data can be converted to ANY numeric data type, not just Decimal(19,6).
For instance, IsNumeric returns a 1 for "2E7", as it could be considered exponential notation.
That value can't be converted directly to Decimal(19,6) though.
This is very annoying, and in my opinion makes the function almost useless.
September 27, 2013 at 10:14 am
mrea-605474 (9/27/2013)
Maybe I am a bit dense today since it is a Friday and I am looking towards the weekend.. But this isn't making sense to me.I have a web form with a QTY in it, it has javascript to only allow numbers, but people disable their javascript and enter crazy things in qty fields sometimes..
SO... I am writing some code and getting the error.
I have simplified it down to this, but still get the error.
Doesn't make sense to me since I am using the ISNUMERIC to only apply the CAST function to records that are actually numbers.
select case when ISNUMERIC(QtyOrdered)=1
then CAST(QtyOrdered AS decimal(19,6))
else 0.0
end as Qty
from orders
Any ideas? Thanks in advance.
Because ISNUMERIC is horrible. It evaluates to true any value that can be cast as any datatype that is roughly a number in some form or another.
This brings up 2 big questions:
1) Since you know that javascript validation is not rock solid why are you not doing serverside validation before inserting your data?
2) Why are using varchar to store numbers? If you want decimal(19,6) then make that your datatype for the column.
Consider the following code:
create table #orders
(
QtyOrdered varchar(10)
)
insert #orders
select '19'
union all select 'Why?'
union all select '1.84736378483933' --This will cause a "String or binary would be truncated." error
union all select '1e3' --This will evaluate to true because it is scientific notation
union all select '$1.40' --This will evaluate to true because it can be converted to money
select case when ISNUMERIC(QtyOrdered)=1
then CAST(QtyOrdered AS decimal(19,6))
else 0.0
end as Qty
from #orders
drop table #orders
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2013 at 10:40 am
Ah well at least I understand why now.
As for suggestions.
1. This is on SQL2008R2...
2. Yes, perhaps at some point I will dig into the web pages & DB structure and start changing it. I know why it was written that way, once they have formatting of the numbers how they want it, they can save it in the DB and then not have to worry about it anytime they show it on the web pages.. But right now I need to get this done without re-inventing the wheel so to speak especially when it doesn't really affect the end customer, unless of course I can't come up with a solution to what I am working on now then it will..
3. I am sure I can come up with another way to handle this, but so far they all stink..
Thanks for the pointers. I will research the links provided and hopefully come up with a better solution.
:unsure:
September 27, 2013 at 11:04 am
You should definitely read that article, it is very good but the solution presented there only works for integers.
I think something like this will get you close:
WHEN ISNUMERIC(Value) = 1 AND Value NOT LIKE '%[^0-9.]%'
This won't prevent numbers larger than Decimal(19,6) though, and values containing commas will be treated as non-numeric.
September 27, 2013 at 12:58 pm
sestell1 (9/27/2013)
You should definitely read that article, it is very good but the solution presented there only works for integers.I think something like this will get you close:
WHEN ISNUMERIC(Value) = 1 AND Value NOT LIKE '%[^0-9.]%'
This won't prevent numbers larger than Decimal(19,6) though, and values containing commas will be treated as non-numeric.
This isn't a bad solution and will probably get most of the annoying issues resolved. Often times if a numeric value is being entered into a web based solution, and you know the scope of the numeric value then you can simply change object to a pick list. Otherwise you need to have better data scrubbing before you attempt to write back to the database.
You have touched on, from my experience, one of the biggest problems I had to face when working with a web based solution. It is amazing how creative people get when they have free reign to entering data. Most secondary work pertaining to web solutions was finding ways to insure clean data coming in.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 30, 2013 at 7:45 am
Now that took care of my issue.
Thanks.
September 30, 2013 at 7:57 am
mrea-605474 (9/27/2013)
Yes, perhaps at some point I will dig into the web pages & DB structure and start changing it. I know why it was written that way, once they have formatting of the numbers how they want it, they can save it in the DB and then not have to worry about it anytime they show it on the web pages.. But right now I need to get this done without re-inventing the wheel so to speak especially when it doesn't really affect the end customer, unless of course I can't come up with a solution to what I am working on now then it will..
You do realize that the reason you are having issues is because it was originally done "right now" instead of "right". It sounds like you are intending to make your fix "right now" instead of "right" too. At some point you have to stop and fix the problem or you will continue to fight the symptoms of a poorly implemented system.
At the very least adding code behind validation to your web form is not any kind of reinvention of the wheel, it is more like filling the wheel with air. If you at least sanitize the data before it hits your system you will not have to worry about trying to deal with all the garbage in your database. Remember, "Garbage In, Garbage Out".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply