Error handling in convert (numeric(8,3),varchar) 2

[lmarks]

I am converting a list of user entered varchar values that should be numeric.

I want to convert the varchar to numeric and I will then insert into a numeric field.
Problem occurs if user enters a screwy value like 185 185.

The SQL conversion of select statement will error
"Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

IF there are 1000 records it is hard to find the offending record.

Is there a Select stmt I can run to find the offending values and correct them prior to running the conversion?


BTW, I have no control over the user input as that is a vendor system and database.

Thank you

 

[bborissov]

SELECT * FROM YourTable WHERE ISNUMERIC(YourField+'e0') = 0

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

faq183-6423

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

The quick and dirty way would be the following SQL:

SELECT * FROM SomeTable WHERE ISNUMERIC(SomeColumn) = 0

This will give you non-numeric entries. But it won't show you entries which are technically numeric but might not be converted into NUMERIC(8,3). If you need a more accurate test, you might have to examine the string with a custom function.

 

[lmarks]

Thank you..this is exactly what I was looking for..
Worked like a charm