We help IT Professionals succeed at work.

Convert/CAST error

EasyAim
EasyAim asked
on
I'm sure this is a common question (but I can't find an answer anywhere else...)

  We've inherited a column that is "varchar" that is actually populated with integers in MOST of the rows.  However, some rows have letters and some are NULL and some are blank.

i.e.

SELECT h.Account_ID, p.Amount
FROM Account_History h, Payment p
WHERE h.Account_ID = 3000679 AND
CAST(h.GeneralText AS int) = p.Payment_ID

The above SQL statement works great up to row 315 where it encounters:  h.GeneralText = "AC33456"
I want to just skip this row (return p.Amount = NULL or zero) and continue on.

 How do I convert the text that is "convertable"  to integers and just skip the others without bombing the SQL statement?

THANKS
Comment
Watch Question

Commented:
How about:

SELECT h.Account_ID, p.Amount
FROM Account_History h, Payment p
WHERE h.Account_ID = 3000679 AND
CASE WHEN ISNUMERIC(h.GeneralText) = 1 THEN CAST(h.GeneralText AS int) ELSE NULL END = p.Payment_ID

Haven't tested it, but I'm sure you can ;-)

Cheers,
Jeremy

Commented:
Another (maybe more elegant) option would be to convert Payment_ID to a varchar, instead of converting GeneralText to an int:

SELECT h.Account_ID, p.Amount
FROM Account_History h, Payment p
WHERE h.Account_ID = 3000679 AND
h.GeneralText = Cast(p.Payment_ID as varchar(50))

Change the length of the cast to varchar to the length of the GeneralText column. I assumed 50 would be enough, but better use the exact needed length.

Author

Commented:
Works great. Thanks.

I haven't tried the "char" method yet.  It may actually be faster.  I'll try it out later.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.