Error converting data type varchar to numeric.

Question

Error converting data type varchar to numeric.

Barkingdog

SSCoach

Points: 18670
More actions
June 16, 2008 at 4:14 pm

#192688

I am inner joining two tables. The join field in one table is numeric; the join field in the other table is a varchar. The script ran fine for months but now it gives the error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
The tables have 50 million+ rows so I can't easily browse them. How can I determine which values (in each table) are causing the problem?
TIA,
barkingdog

Viewing 8 posts - 1 through 8 (of 8 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The issue apparently is that you have alphanumeric values in the second table's join field, which can't be cast to a numeric value, so the link is "invalid".

You could do one of two things:

- force the first table's JOIN field to be evaluated as a VARCHAR by casting the numeric value as a VARCHAR(n). For performance purposes, I'd recommend making a computed column that does exactly that, so that you can index it.

- go find any alphanumeric value in table #2. I would start by ordering on the join field, and browse the beginning and ending values to clean those up. After that you'd have to start using PATINDEX to pull out anything non-numeric ('%[^1-9]%').

Either way - this implicit cast has got to be slowing your joins down a LOT. You should look at fixing it (or using the computed column as describved earlier), so that the indexes can really be leveraged.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Select * from Table2

Where IsNumeric(JoinColumn) = 0

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

rbarryyoung (6/16/2008)
Select * from Table2
Where IsNumeric(JoinColumn) = 0

Maybe... or maybe not... 😉

DECLARE @Varchar VARCHAR(10)

DECLARE @Numeric NUMERIC

SET @Varchar = '$1.00'

SET @Numeric = 1

SELECT ISNUMERIC(@Varchar),

ISNUMERIC(@Numeric)

SELECT 1 WHERE @Varchar = @Numeric

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

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Jeff is absolutely right, use of ISNUMERIC is limited, it will miss many values. You can try it - if it helps, great; but you can't be sure it finds all problematic places.

WHERE column_name LIKE '%[^0-9]%' is more reliable if you want to find entries containing non-numeric characters.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

I think that you have to at a minimum include '. + -' in that list.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

rbarryyoung (6/17/2008)
I think that you have to at a minimum include '. + -' in that list.

And, a decimal point... but then you run into things like having two decimal points. Best thing to do is change the VARCHAR column to NUMERIC so such mistakes cannot happen.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Jeff Moden (6/17/2008)
rbarryyoung (6/17/2008)
I think that you have to at a minimum include '. + -' in that list.
And, a decimal point...

it's in there, it's just hard to see... 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]