|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
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, Tam You can use the ISNUMERIC() to determine whether an
expression is a valid numeric type. Show quote "Tam Vu" <vuht2***@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: Hi Joe,> >CASE WHEN >REPLACE( > REPLACE( > .. > REPLACE (num '0',''), > 1, ''), > .. > 9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END 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 Yep. But in SQL Server 2005
Show quote "Tam Vu" <vuht2***@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 > >> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Show quote
Should be > CASE WHEN num NOT LIKE '%^[0-9]%' THEN CAST(num AS INTEGER) END "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: > >>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) On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>>> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END Hi Roji,>Should be >> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CAST(num AS INTEGER) END 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 >> CASE WHEN num NOT LIKE '%[^0-9]%' THEN CAST(num AS INTEGER) END (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 In a hurry to correct you asap, I refuse to look at the finer details :)> (since it's an exact same copy - you obviously forgot to correct the > mistake before posting). :) Infact I have'nt noticed the misplaced caret, but only the mispelled CAST. Lets keep correcting each other :p Show quote "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: > >>>> 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 > > 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 > >>> CASE WHEN num NOT LIKE '%[^0-9]%' THEN CAST(num AS INTEGER) END > > (Note how the caret has sneaked one position to the right) > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) On Tue, 23 Aug 2005 10:22:02 +0530, Roji. P. Thomas wrote:
>> You are correct that I made a mistake. But your correction is wrong too Hi Roji,>> (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. Would you believe that I looked at my original post and your correction several times, comparing them character by character, and ended up not spotting the difference? This is so embarrassing - I *know* that my finger always want to type an E after CAS; it's the single most common syntax error in my queries - I really should have checked that part better when I saw your correction. >Lets keep correcting each other :p I'll be keeping a close eye on your posts! ;->Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  
Other interesting topics
|
|||||||||||||||||||||||
