Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Catch numeric conversion errors and KEEP GOING Expand / Collapse
Author
Message
Posted Wednesday, December 17, 2008 9:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 19, 2012 2:00 PM
Points: 14, Visits: 76
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....
Post #621345
Posted Wednesday, December 17, 2008 9:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:05 PM
Points: 20,738, Visits: 32,520
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #621353
Posted Wednesday, December 17, 2008 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 19, 2012 2:00 PM
Points: 14, Visits: 76
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:
TOM 95.5
DICK 101
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:
DICK 101
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,

Post #621396
Posted Wednesday, December 17, 2008 4:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 35,370, Visits: 31,911
SELECT * FROM #MARKTABLE
WHERE MARK NOT LIKE '%[^0-9.]%'



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #621710
Posted Thursday, December 18, 2008 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 19, 2012 2:00 PM
Points: 14, Visits: 76
Well, straightforward enough! I figured I was missing something fairly simple. Thanks!
Post #622264
Posted Thursday, December 18, 2008 7:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 35,370, Visits: 31,911
Heh... thanks for the feedback... didn't mean for it to be so stark. Any questions on how that works?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #622559
Posted Thursday, December 18, 2008 8:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 19, 2012 2:00 PM
Points: 14, Visits: 76
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! ;)
Post #622565
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse