Professional Web Applications Themes

REGEXP Clarification - MySQL

Greetings: I am trying to locate incorrect data in a column using REGEXP. Only numeric data is allowed but I am importing data, some of which is not properly formatted. I am using: SELECT * FROM `table` WHERE `column` REGEXP "[^[:digit:]]"; to locate any value that is not a number. I've tried several variations and I get an empty set back. I know that there are illegal characters in some of the data but I can't seem to locate it using this query. The field can contain anywhere from 1 to 6 digits, if that is relevant. Advice is much ...

  1. #1

    Default REGEXP Clarification

    Greetings:

    I am trying to locate incorrect data in a column using REGEXP. Only
    numeric data is allowed but I am importing data, some of which is not
    properly formatted. I am using:

    SELECT *
    FROM `table`
    WHERE `column` REGEXP "[^[:digit:]]";

    to locate any value that is not a number. I've tried several variations
    and I get an empty set back. I know that there are illegal characters
    in some of the data but I can't seem to locate it using this query. The
    field can contain anywhere from 1 to 6 digits, if that is relevant.
    Advice is much appreciated.
    --

    Regards,

    Jeff Gardner
    ___________________________

    "Contrary to popular belief, Unix is user friendly. It just happens
    to be very selective about who its friends are." --Kyle Hearn
    Jeff Guest

  2. #2

    Default Re: REGEXP Clarification


    "Jeff Gardner" wrote... 

    Could you post an example of the "invalid" data,
    on my 5.1.12 you query works.


    Regards
    Dimitre


    Radoulov, Guest

  3. #3

    Default Re: REGEXP Clarification

    Radoulov, Dimitre wrote: 
    >
    > Could you post an example of the "invalid" data,
    > on my 5.1.12 you query works.
    >
    >
    > Regards
    > Dimitre
    >
    >[/ref]
    The program that I am importing into tells me that one of the fields
    contains a period. I am using mysql from the command line to "sanitize"
    the data before final import. If I find it, I'll post it.

    --

    Regards,

    Jeff Gardner
    ___________________________

    "Contrary to popular belief, Unix is user friendly. It just happens
    to be very selective about who its friends are." --Kyle Hearn
    Jeff Guest

  4. #4

    Default Re: REGEXP Clarification

    Jeff Gardner wrote: 
    >>
    >> Could you post an example of the "invalid" data,
    >> on my 5.1.12 you query works.
    >>
    >>
    >> Regards
    >> Dimitre
    >>[/ref]
    > The program that I am importing into tells me that one of the fields
    > contains a period. I am using mysql from the command line to "sanitize"
    > the data before final import. If I find it, I'll post it.
    >[/ref]
    It turns out that there were 13 rows with a NULL value in the field in
    question. NULL is not whitespace, nor is it digits. Any explanation as
    to why the above query returned no rows?

    --

    Regards,

    Jeff Gardner
    ___________________________

    "Contrary to popular belief, Unix is user friendly. It just happens
    to be very selective about who its friends are." --Kyle Hearn
    Jeff Guest

  5. #5

    Default Re: REGEXP Clarification


    "Jeff Gardner" wrote... [/ref][/ref]
    [...] 

    Because of its special meaning, I suppose.

    "Conceptually, NULL means "a missing unknown value" and it is treated
    somewhat differently from other values. "

    Unknown means that you cannot _know_ whether it's a digit or something else
    (btw, whitespaces are handled correctly with your example query).

    So,
    a) Use constraints (not null)
    or
    b) Add "column is not null" in your query


    Regards
    Dimitre


    Radoulov, Guest

  6. #6

    Default Re: REGEXP Clarification


    "Radoulov, Dimitre" wrote... [/ref]
    > [...] 
    >
    > Because of its special meaning, I suppose.
    >
    > "Conceptually, NULL means "a missing unknown value" and it is treated
    > somewhat differently from other values. "[/ref]


     


    I ment "as you very likely expect", not "correctly" :)


    Regards
    Dimitre


    Radoulov, Guest

Similar Threads

  1. Clarification
    By Wes in forum PHP Development
    Replies: 3
    Last Post: December 31st, 12:02 AM
  2. Clarification please
    By John Carlson in forum Informix
    Replies: 12
    Last Post: August 23rd, 03:43 AM
  3. One Clarification
    By ss in forum ASP.NET General
    Replies: 2
    Last Post: August 19th, 02:56 PM
  4. Clarification
    By Sam Poikail in forum Macromedia Fireworks
    Replies: 0
    Last Post: August 15th, 04:40 AM

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