Results 1 to 6 of 6

Thread: convert error

  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: convert error

    Get error when i run query based on a View

    i run:
    select ISNUMERIC(SR.code) as Code, SR.code, SR.text
    from ScanResult SR
    --where SR.code = 1

    It works fine and gives me this result:
    Code code text
    1 134 Unable to archive file
    1 34 File archived
    1 135 Unable to block file
    1 35 Block successfully

    When i run this
    select ISNUMERIC(SR.code) as Code, SR.code, SR.text
    from ScanResult SR
    where SR.code = 1

    It gives me
    "Conversion failed when converting the varchar value '0x04000018' to data type int."

    "Code" should be INT and "text" is nvarchar

    "ScanResult" is a View and in that i have
    SELECT CAST(CODE AS INT) AS CODE
    and
    WHERE ISNUMERIC (CODE) =1

    but still i got the error message. Can someone please explain this?


    Thx in advance

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The first thing to remember is that ISNUMERIC does not mean that the number can be translated to a particular datatype. It really should be called LOOKSNUMERIC. People have been getting caught up by this, as these all pass ISNUMERIC:
    Code:
    select isnumeric ('$100'), isnumeric('100e01'), isnumeric ('100d01')
    The CAsT function is getting caught up in how to treat the 'x' in the value above. If presented as a hexadecimal string, it will convert easily. But as an nvarchar string, it gets confused.

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    ah thanks alot, now i have to found a nice solution to this and yes it should be called LOOKSNUMERIC

  4. #4
    Join Date
    Mar 2004
    Posts
    162
    damit, can anyone give me a hint on how to sort out those hex value, plain text and convert the rest to INT?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How about the old double negative:
    Code:
    create table test1
    (col1 varchar(20))
    
    insert into test1 values
    	('1234'),
    	('0x1234'),
    	('hello'),
    	('1')
    
    select *
    from test1
    where col1 not like '%[^0-9]%'

  6. #6
    Join Date
    Mar 2004
    Posts
    162
    Of course, work like charm. Thx alot

Posting Permissions

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