Catch numeric conversion errors and KEEP GOING

Question

Catch numeric conversion errors and KEEP GOING

cvgalante

SSChasing Mays

Points: 654
More actions
December 17, 2008 at 9:14 am

#126542

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....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

cvgalante SSChasing Mays Points: 654 More actions · Answer 2

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,

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cvgalante SSChasing Mays Points: 654 More actions · Answer 4

Well, straightforward enough! I figured I was missing something fairly simple. Thanks!

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

Heh... thanks for the feedback... didn't mean for it to be so stark. :hehe: 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cvgalante SSChasing Mays Points: 654 More actions · Answer 6

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! 😉