How to trap an error from insert ?

When inserting rows from a staging table to a production one, I need to
convert a column of type varchar to type int. Often there're rows that
have junk data in this column and that makes convert() fail. Is it
possible to know that such junk rows exist without getting an error
message ? In other words, is there a way to prevent the insert query
from throwing an error msg, but I still know that it fails ?
thanks,
TamYou can use the ISNUMERIC() to determine whether an
expression is a valid numeric type.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124509954.485547.268620@.o13g2000cwo.googlegroups.com...
> When inserting rows from a staging table to a production one, I need to
> convert a column of type varchar to type int. Often there're rows that
> have junk data in this column and that makes convert() fail. Is it
> possible to know that such junk rows exist without getting an error
> message ? In other words, is there a way to prevent the insert query
> from throwing an error msg, but I still know that it fails ?
> thanks,
> Tam
>|||I've tried isnumeric() - indeed this was the first thing I did. Yet
strings that have character like 'd', 'e', '.' also pass isnumeric()
but are not convertible to int, and these characters happen quite
commonly in the junk rows in my DB. Any other suggestions ?|||You can check for all digits with:
CASE WHEN
REPLACE(
REPLACE(
.
REPLACE (num '0',''),
1, ''),
.
9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END|||On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:

>You can check for all digits with:
>CASE WHEN
>REPLACE(
> REPLACE(
> ..
> REPLACE (num '0',''),
> 1, ''),
> ..
> 9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END
Hi Joe,
Never rely on implicit conversion if you don't have to. Use quotes
around 1, 2, ..., 9 as well to prevent conversions.
And of course, this is lots more complicated then encessary:
CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||these are interesting solutions. btw, is there any "try-catch"
structure in sql server as I'm concerned there're cases that exceptions
are not known before hand ?
thanks,
Tam|||>> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Should be
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CAST(num AS INTEGER) END
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:kgrgg11r74k425gau3958b7dgjjtcsu9vu@.
4ax.com...
> On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:
>
> Hi Joe,
> Never rely on implicit conversion if you don't have to. Use quotes
> around 1, 2, ..., 9 as well to prevent conversions.
> And of course, this is lots more complicated then encessary:
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep. But in SQL Server 2005
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124668962.565306.81090@.g43g2000cwa.googlegroups.com...
> these are interesting solutions. btw, is there any "try-catch"
> structure in sql server as I'm concerned there're cases that exceptions
> are not known before hand ?
> thanks,
> Tam
>|||On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>Should be
Hi Roji,
You are correct that I made a mistake. But your correction is wrong too
(since it's an exact same copy - you obviously forgot to correct the
mistake before posting).
For others reading this discussion: the correct statement is
(Note how the caret has sneaked one position to the right)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
:)
In a hurry to correct you asap, I refuse to look at the finer details :)
Infact I have'nt noticed the misplaced caret, but only the mispelled CAST.
Lets keep correcting each other :p
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1mkg19d2vmgq7vvus5ncer3di83gdvhut@.
4ax.com...
> On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
>
> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
> For others reading this discussion: the correct statement is
>
> (Note how the caret has sneaked one position to the right)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)