Professional Web Applications Themes

How to use IsNumeric function in WHERE clause? - MySQL

I need to test a field value in the WHERE clause of a SELECT statement. With Microsoft database products, it would look like this: WHERE IsNumeric(MyField) If I have a text field that contains an account number such as 9209834, for example, the function returns true; but if the account number is LAX-0933V, the function returns false. I've found a couple of possible ways to do this in MySql, but I'm not sure what the best approach is: (a) WHERE CONVERT(MyField, SIGNED INTEGER) IS NOT NULL (b) WHERE myField REGEXP ('[0-9]') I'm inclined to go with option (a). Is there ...

  1. #1

    Default How to use IsNumeric function in WHERE clause?

    I need to test a field value in the WHERE clause of a SELECT statement. With
    Microsoft database products, it would look like this:

    WHERE IsNumeric(MyField)

    If I have a text field that contains an account number such as 9209834, for
    example, the function returns true; but if the account number is LAX-0933V, the
    function returns false.

    I've found a couple of possible ways to do this in MySql, but I'm not sure what
    the best approach is:

    (a) WHERE CONVERT(MyField, SIGNED INTEGER) IS NOT NULL

    (b) WHERE myField REGEXP ('[0-9]')

    I'm inclined to go with option (a).

    Is there a better way?

    deko Guest

  2. #2

    Default Re: How to use IsNumeric function in WHERE clause?

    deko wrote:
    > I need to test a field value in the WHERE clause of a SELECT statement.
    > With Microsoft database products, it would look like this:
    >
    > WHERE IsNumeric(MyField)
    >
    > If I have a text field that contains an account number such as 9209834,
    > for example, the function returns true; but if the account number is
    > LAX-0933V, the function returns false.
    >
    > I've found a couple of possible ways to do this in MySql, but I'm not
    > sure what the best approach is:
    >
    > (a) WHERE CONVERT(MyField, SIGNED INTEGER) IS NOT NULL
    >
    > (b) WHERE myField REGEXP ('[0-9]')
    >
    > I'm inclined to go with option (a).
    MySQL's CONVERT() function is for translating character sets, not
    parsing numeric values from strings.

    CAST(MyField AS SIGNED INTEGER) won't work the way you want it to,
    either. It doesn't return NULL when it ps alpha or mixed
    alpha-numeric strings. It returns the numeric value of any leading
    numeric characters in the string, or 0 if no leading numeric characters
    are found.

    For example:

    SELECT CAST('123abc' AS SIGNED INTEGER);
    returns numeric value 123.

    SELECT CAST('abc132' AS SIGNED INTEGER);
    returns numeric value 0.

    So you'll have to use a regular expression.

    But WHERE myField REGEXP ('[0-9]') won't work either. It matches the
    string if the string contains a single digit character, but the string
    may contain other non-numeric characters.

    Any of the following should work:

    WHERE myField REGEXP '^[0-9]+$'
    WHERE myField REGEXP '^[[:digit:]]+$'
    WHERE myField NOT REGEXP '[^0-9]'

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: How to use IsNumeric function in WHERE clause?

    > Any of the following should work:
    >
    > WHERE myField REGEXP '^[0-9]+$'
    > WHERE myField REGEXP '^[[:digit:]]+$'
    > WHERE myField NOT REGEXP '[^0-9]'
    I'm testing now.

    WHERE myField REGEXP '^[0-9]+$'

    Should do it.

    Thanks for the tip!

    deko Guest

Similar Threads

  1. Using IN in a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 15
    Last Post: September 5th, 05:05 PM
  2. CF MX 6 WHERE Clause
    By Jeremy5431 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: May 2nd, 02:26 PM
  3. isNumeric in Query - Help
    By edhusar in forum Macromedia ColdFusion
    Replies: 3
    Last Post: April 20th, 05:14 PM
  4. isNumeric Problem
    By hakim in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: March 4th, 02:04 PM
  5. IsNumeric Bug?
    By Mike Sarbu in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 3rd, 10:21 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