Safe char2numeric conversion


I'm trying to convert a char(8) column to numeric(8),
but I have some SQL-bugs/performance problems.

I have a table with
that is, mostly good numeric values but some illegal values.
First I did a loop like:

For each row
  IF isnumeric(<column value>) = 1
      select @newColValue = convert(numeric(8),<column value>)
     discard this error row

The problem is a SQL v6.5 bug. isnumeric('0001E000') returns 1!
So, I re-wrote the test:
IF isnumeric(<column value>) AND patindex('%[A-E]%',<column value>) = 0

This worked fine, but is very much too slow:(

My last idea was to try to convert and then "catch" conversion errors.

select @newColValue = convert(numeric(8),<column value>)
if @@ERROR <> 0
  discard this error row

Unfortunatly, the severity of the conversion error is too high,
so my procedure is interrupted. I can't change the global severity
of this error.

Any other ideas? I need to be able to convert 1000 000 rows with
10 numeric values each in less than a few hours.

Who is Participating?
bretConnect With a Mentor Commented:
Seems to me the main reason this is so slow is probably the line by line processing.  Have you tried it as a batch?

select convert(numeric(8),<col>) where
<col> like "[ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][ 0-9][0-9]"
That's not a bug.

'0001E000' is a valid numeric expression where the 'E' means that it's in scientific notation.

The letter E should be the only one that gives you problems, so I don't understand why you were looking for 'A-E'.

You are going to have to decide if you want to drop the numbers using scientific notation, i.e. does a number with an 'E' represent bad data in your particular situation?

Let me know if that's the case.
eklasAuthor Commented:
Ok, but how come:
isnumeric('000D0') returns 1?
I need to check for a plain integer. In other words, I want to ensure that
convert(numeric(8),<column value>)  succeeds.

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

If that's the case, then you're probably having problems with A through F.

It looks like your isnumeric function is interpreting hexadecimal numbers to be numeric.

Numbers in hex include the letters A-F.

Let me take a look at it and see if I can give you a good solution.
Out of curiousity, where are you getting your isnumeric function from?

Is it a stored proc?  Or is it something new that comes with 7.0?

Or is that just pseudocode above and you're actually using VB or something?

As soon as I posted that, I found it.  For the curious, apparently it's new to 6.5.

I was accidently looking at an old version of MSDN.
eklasAuthor Commented:
Doing it in one single expression without the loop consumes too much memory
I believe.
I guess there is no good solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.