Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Select Error

  1. #1
    Join Date
    Jul 2008
    Posts
    16

    Unanswered: Select Error

    Hi

    Is is possible to find which row in a dataset is causing a select error.

    For example:
    Code:
    SELECT CAST(FieldOfStrings AS FLOAT) FROM TableWithStrings
    raises an error if one of the strings cannot be converted. I want to know which row produced the error - using say, @@ROWCOUNT.

    TIA

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    SELECT FieldOfStrings FROM TableWithStrings
    WHERE isNumeric(FieldOfStrings) = 0 or FieldOfStrings is null
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Strictly speaking that can miss "dodgy" entries.
    Code:
    declare @TableWithStrings AS TABLE (FieldOfStrings VARCHAR(10))
    
    INSERT @TableWithStrings
    SELECT    '0.1'
    UNION ALL 
    SELECT    '1E0'
    UNION ALL 
    SELECT    '¤+,.'
    
    SELECT    *
    FROM    @TableWithStrings
    
    SELECT    *
    FROM    @TableWithStrings
    WHERE    isNumeric(FieldOfStrings) = 0 or FieldOfStrings is null
    
    SELECT FieldOfStrings FROM @TableWithStrings
    WHERE FieldOfstrings LIKE '%[^0-9.]%' or FieldOfStrings is null
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I thought we were looking for the bad ones?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depends on what is defined as "bad". Usually, stuff isn't deliberately stored in scientific notation, so not everything that can be converted to a numeric is necessarily good.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another example - infinity is numeric but can't be converted to float.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Another example - infinity is numeric but can't be converted to float.
    but so easy to store as VARCHAR, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Actually, strictly speaking infinity is not numeric. If anything, you should store it as a NULL because it's value is unknown/undefined
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    george gets the gold star.

    the fresh roasted coffee beans I got this morning have me too wired up.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ya needs to tell MS - because isnumeric evaluates to true for infinity.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    how do you represent infinity in sql? this is a new one on me.

    well i will be damned. i had no idea there was a sql representation for this...

    http://blog.falafel.com/2008/07/10/D...SQLServer.aspx

    http://sql-server-performance.com/Co...77/142223.aspx

    of course the only time I legitimately used floats in an application was for some engineering stuff I never saw to fruition. otherwise I avoid them.

    I still learn something new every now and then.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    how do you represent infinity in sql?
    try 1 / 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by georgev
    If anything, you should store it as a NULL because it's value is unknown/undefined
    mathematically, infinity is not undefined. in fact there are an infinite number of ways to define it. different infinities can be compared to each other - some infinities are "bigger" than others.

    For example, you can compare these:

    1) limit of x as x->inf

    2) limit of x^2 as x->inf

    3) limit of x^3 as x->inf

    #2 is infinitely bigger than #1, likewise for #3 and #2

    here's another one: which is bigger, the set of all integers? or the set of all real numbers between 0 and 1?

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can do this before running the convert:
    Code:
    use tempdb
    go
    select
       FieldOfStrings = cast(object_id as varchar(25))
       into #tmp
       from sys.indexes
    go
    insert #tmp select 'ABC123'
    go
    select
       RecordNumber
      ,FieldOfStrings
       from (
          select
             RecordNumber = row_number() over(order by FieldOfStrings)
            ,FieldOfStrings
             from #tmp
       ) x
       where isnumeric(FieldOfStrings) = 0
    go
    drop table #tmp
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and speaking of infinity, this is the max number I got from float(53):
    Code:
    179000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.000000
    But something else I noticed, - after 1.79E+14 + 82099999999999999 all precision is lost:
    Code:
    declare @f float(53)
    set @f = 1.79E+14 + 820999999999999
    select @f, @f + 1
    Last edited by rdjabarov; 07-14-08 at 03:10.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •