Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Safe char2numeric conversion

Posted on 1999-06-22
Medium Priority
Last Modified: 2010-03-19

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.

Question by:eklas
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2

Expert Comment

ID: 1095862
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.

Author Comment

ID: 1095863
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.


Expert Comment

ID: 1095864
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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 10

Accepted Solution

bret earned 200 total points
ID: 1095865
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]"

Expert Comment

ID: 1095866
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?

Expert Comment

ID: 1095867

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.

Author Comment

ID: 1095868
Doing it in one single expression without the loop consumes too much memory
I believe.
I guess there is no good solution.

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question