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,