Professional Web Applications Themes

Comparing varchar to number in SQL2000 - Microsoft SQL / MS SQL Server

You might want to try using WHERE ISNUMERIC(Column1)=1 (See http://www.aspfaq.com/2390 for limitations) Also, if the column is supposed to be numeric, clean out the non-numeric data and make it numeric! "Diane Yocom" <gov> wrote in message news:#qL#phx.gbl...  this ...

  1. #1

    Default Re: Comparing varchar to number in SQL2000

    You might want to try using WHERE ISNUMERIC(Column1)=1

    (See http://www.aspfaq.com/2390 for limitations)

    Also, if the column is supposed to be numeric, clean out the non-numeric
    data and make it numeric!





    "Diane Yocom" <gov> wrote in message
    news:#qL#phx.gbl... 
    this 


    Aaron Guest

  2. #2

    Default Re: Comparing varchar to number in SQL2000

    Jacco and Diane,

    In SQL 7, the column type always took precedence in a
    comparison between a column and a value/variable/expression.
    In SQL 2000, the comparison is made using the higher-precedence
    data type, without regard to where the values come from. This
    is a more sensible thing to do, but as you see, it can cause problems.
    In fact, it can cause major changes to efficiency, if an index can no
    longer be used because of a type conversion.

    The only thing that might work in terms of a setting is to put the
    database into 70 compatibility mode, but you will lose other benefits
    of 2000. Better to rewrite the queries so that any expressions are of
    the same type as the columns they are being compared against, using
    CAST or CONVERT as necessary.

    -- Steve Kass
    -- Drew University
    -- Ref: 165CF8C2-4ABC-42EE-826C-EF94B718F92E

    Jacco Schalkwijk wrote:
     
    >this
    >

    >
    >
    >
    >[/ref]

    Steve Guest

  3. #3

    Default Re: Comparing varchar to number in SQL2000

    Yes, there was a change from SQL7 to SQL2K, most notably with queries in
    the syntax:

    SELECT .. FROM .. WHERE Column = value

    If Column and value are of different data type, SQL7 would convert the
    value to the data type of Column. SQL2000 will convert column/value
    with the lowest Data Type Precedence to the on with the highest
    Precedence.

    This change in implementation can also cause performance degradation
    when upgrading from SQL7 to SQL2000. For example, a query satisfied with
    an INDEX SEEK in SQL7 could become an INDEX SCAN in SQL2000.

    Gert-Jan


    Jacco Schalkwijk wrote: 
    > this [/ref]
    Gert-Jan Guest

  4. #4

    Default Re: Comparing varchar to number in SQL2000

    Thanks very much for all the responses. We ended up setting the database to
    70 compatibility and that fixed the problem. At least it's a temporary
    solution!

    "Steve Kass" <edu> wrote in message
    news:phx.gbl... [/ref]
    2000 [/ref]
    change [/ref][/ref]
    which [/ref]
    > [/ref][/ref]

    > >this
    > >
    > > [/ref][/ref]
    than 
    > >
    > >
    > >
    > >[/ref]
    >[/ref]


    Diane Guest

  5. #5

    Default Re: Comparing varchar to number in SQL2000

    Try:

    SELECT Field1, Field2 FROM tblTest WHERE Field1 > '0'

    --
    -Vishal
    Diane Yocom <gov> wrote in message
    news:#qL#phx.gbl... 
    this 


    Vishal Guest

  6. #6

    Default Re: Comparing varchar to number in SQL2000

    Thanks for the explanation Steve.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Steve Kass" <edu> wrote in message
    news:phx.gbl... [/ref]
    2000 [/ref]
    change [/ref][/ref]
    which [/ref]
    > [/ref][/ref]

    > >this
    > >
    > > [/ref][/ref]
    than 
    > >
    > >
    > >
    > >[/ref]
    >[/ref]


    Jacco Guest

Similar Threads

  1. Can CompareNoCase comparing number?
    By alecken in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: April 15th, 02:04 PM
  2. sql2000 backwards
    By ranger in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 13th, 11:02 AM
  3. CF, SQL2000, and XML
    By Osgyth in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 3rd, 08:59 PM
  4. DB access SQL2000 works usually
    By cf_code_warrior in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 2nd, 10:38 PM
  5. iis/asp + sql2000 a bit slow
    By Florian in forum ASP Database
    Replies: 12
    Last Post: February 8th, 10:13 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139