December 17, 2008 at 9:14 am
I'm pretty frustrated! I am working with the data back end in an existing program that stores student marks (A, B, I, P, F, or numbers and some other values). I cannot control what the users put in the Marks column which is VARCHAR(10). I need to produce a query that compares numeric marks against certain values and ignores everything else - for instance, MARK > 100. It should be a single SELECT query, because the program's custom report generator simple executes that and returns the columns. However, I may be able to execute a stored procedure that returns a table with the report generator if necessary.
My problem is this: T-SQL ISNUMERIC function considers certain things numeric that are not. For instance, a hyphen. However, it will subsequently fail to convert or cast properly to a numeric data type. Most error trapping methods, like @@ERROR or TRY...CATCH, will stop the processing, in which case the custom report gives the user nothing (except the message "No tables in dataset"). I can't use TRY...CATCH within a user-defined function either. I don't want to just send the user an error message.
How can I construct a query that will simply ignore those records that fail to convert and produce a result? Also, how do I pinpoint the records that are causing the problem and perhaps report those to the user? I'm going to hate myself if I am missing something simple here....
December 17, 2008 at 9:21 am
It would help if you could provide the table DDL (create statement), some sample data (in the form of insert statements), and what your expected results would be based on the sample data.
If you take a few minutes to read the article referenced below regarding Asking for assistance, and follow the guidelines in the article, we can probably help you out relatively easily.
December 17, 2008 at 9:41 am
Thanks Lynn. I was thinking about not revealing sensitive data, but let's look at a simple example:
CREATE TABLE #MARKTABLE
(STUNAME VARCHAR(50),
MARK VARCHAR(10))
INSERT #MARKTABLE VALUES ('TOM','95.5')
INSERT #MARKTABLE VALUES ('DICK','101')
INSERT #MARKTABLE VALUES ('HARRY','-')
Now we execute:
SELECT * FROM #MARKTABLE
WHERE ISNUMERIC(MARK)=1
And we get:
TOM95.5
DICK101
HARRY-
But execute this:
SELECT * FROM #MARKTABLE
WHERE ISNUMERIC(MARK)=1 AND CAST(MARK AS NUMERIC)>100.0
And the result is:
Msg 8115, Level 16, State 6, Line 2
Arithmetic overflow error converting varchar to data type numeric.
I want one query (or perhaps a procedure) that ignores the hyphen and produces this:
DICK101
since Dick is the only student with a Mark over 100. I also need to check for marks > 4, marks = 0, etc. Why does T-SQL not have a real ISNUMERIC function that can tell if a conversion will work?
Thanks,
December 17, 2008 at 4:38 pm
SELECT * FROM #MARKTABLE
WHERE MARK NOT LIKE '%[^0-9.]%'
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2008 at 10:01 am
Well, straightforward enough! I figured I was missing something fairly simple. Thanks!
December 18, 2008 at 7:37 pm
Heh... thanks for the feedback... didn't mean for it to be so stark. :hehe: Any questions on how that works?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2008 at 8:30 pm
No, I get it. I really just haven't paid enough attention to the pattern matching options in LIKE. Plus, I got hung up on trying to work the ISNUMERIC function and didn't give any thought to analyzing the contents of the field. Stark or not, the help is always appreciated! 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply