Professional Web Applications Themes

Weird Results (found w/LIKE, but not with exact match) - MySQL

There is some strange behaviour with one of my tables in MySQL. When I perform a query with an exact match, the result set comes back empty. When I run it with a LIKE clause, I get some results. Does Not Work: mysql -u prod -h mysqlsrv ws_history_stg -e "SELECT * FROM MSG WHERE ORDER_REF = '20061108/RAPTEST/RDL611004643' ORDER BY LOG_TIMESTAMP" Works: mysql -u prod -h mysqlsrv ws_history_stg -e "SELECT * FROM MSG WHERE ORDER_REF LIKE '20061108/RAPTEST/RDL611004643%' ORDER BY LOG_TIMESTAMP" There is certainly 1 record in MSG where MSG.ORDER_REF = '20061108/RAPTEST/RDL611004643' Why should MySQL not return it for the first query?...

  1. #1

    Default Weird Results (found w/LIKE, but not with exact match)

    There is some strange behaviour with one of my tables in MySQL. When I
    perform a query with an exact match, the result set comes back empty.
    When I run it with a LIKE clause, I get some results.

    Does Not Work:

    mysql -u prod -h mysqlsrv ws_history_stg -e "SELECT * FROM MSG WHERE
    ORDER_REF = '20061108/RAPTEST/RDL611004643' ORDER BY LOG_TIMESTAMP"

    Works:

    mysql -u prod -h mysqlsrv ws_history_stg -e "SELECT * FROM MSG WHERE
    ORDER_REF LIKE '20061108/RAPTEST/RDL611004643%' ORDER BY LOG_TIMESTAMP"

    There is certainly 1 record in MSG where MSG.ORDER_REF =
    '20061108/RAPTEST/RDL611004643'

    Why should MySQL not return it for the first query?

    yankeerivera@yahoo.com Guest

  2. #2

    Default Re: Weird Results (found w/LIKE, but not with exact match)

    In article <googlegroups.com>,
    com writes:
     
     

    Probably because your order_ref ends with some whitespace.
    Harald Guest

  3. #3

    Default Re: Weird Results (found w/LIKE, but not with exact match)


    Harald Fuchs wrote: 

    >
    > Probably because your order_ref ends with some whitespace.[/ref]

    Yes, I thought so, but that is not the case. I dumped the whole table
    to a file and I checked. The value _is_ present and it does _not_
    contain any leading or trailing whitespaces.

    yankeerivera@yahoo.com Guest

  4. #4

    Default Re: Weird Results (found w/LIKE, but not with exact match)

     
    >> 
    >>
    >> Probably because your order_ref ends with some whitespace.[/ref]
    >
    > Yes, I thought so, but that is not the case. I dumped the whole table
    > to a file and I checked. The value _is_ present and it does _not_
    > contain any leading or trailing whitespaces.[/ref]

    .... or may be some non printable character(s),
    check with:

    select length(ORDER_REF) from MSG;

    or

    use cat -v on your dump file.


    Regards
    Dimitre


    Radoulov, Guest

Similar Threads

  1. Weird results
    By Alexander Mueller in forum MySQL
    Replies: 2
    Last Post: February 21st, 10:20 AM
  2. ambiguous match found
    By Sam in forum ASP.NET Building Controls
    Replies: 3
    Last Post: August 18th, 09:08 AM
  3. exact string match?
    By Rkl in forum PERL Beginners
    Replies: 8
    Last Post: September 3rd, 05:28 AM
  4. Colors don't match in photoshop, so weird
    By Esco Grande in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 4
    Last Post: August 3rd, 02:54 AM
  5. Returning results where the match is not always there!!!
    By Sh0t2bts in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: June 30th, 09:31 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