Professional Web Applications Themes

BUG with LIKE and binary data - Microsoft SQL / MS SQL Server

No schema but its easy to reproduce. Create a table with a couple of columns (VC, VB) of type varchar and varbinary. VC = 'Test' VB = 0x123456 Now do something like This works and returns the row SELECT * FROM Table WHERE VC LIKE %st% BUT this fails (Hex - 0x25 = '%') SELECT * FROM table WHERE VB LIKE 0x25345625 However if we omit the last '%' (0x25) it works SELECT * FROM table WHERE VB LIKE 0x253456 -- Michael Tissington Oaklodge Technologies http://www.oaklodge.com/technology...

  1. #1

    Default BUG with LIKE and binary data

    No schema but its easy to reproduce.

    Create a table with a couple of columns (VC, VB) of type varchar and
    varbinary.

    VC = 'Test'
    VB = 0x123456

    Now do something like

    This works and returns the row

    SELECT * FROM Table WHERE VC LIKE %st%

    BUT this fails (Hex - 0x25 = '%')

    SELECT * FROM table WHERE VB LIKE 0x25345625

    However if we omit the last '%' (0x25) it works

    SELECT * FROM table WHERE VB LIKE 0x253456

    --
    Michael Tissington
    Oaklodge Technologies
    http://www.oaklodge.com/technology



    Michael Guest

  2. #2

    Default Re: BUG with LIKE and binary data

    Michael,

    LIKE is a string function, and binary values are converted to
    the type of the column you are comparing against. If it is Unicode,
    you may need LIKE 0x2500530054002500, and if it is fixed-length
    instead of nvarchar, the conversion will padd the binary string with
    spaces if necessary.

    Steve Kass
    Drew University

    Michael Tissington wrote:
     

    Steve Guest

  3. #3

    Default Re: BUG with LIKE and binary data

    Interesting, then it must be something strange about my data because this
    fails

    create table Tissington ( VC varchar(40), VB varbinary(40))
    insert into Tissington (VC, VB) values
    ('Test',0x11220000bed5e70f3cad1946a4defdb66c710920 )

    SELECT * FROM Tissington WHERE VB LIKE 0x25b66c71092025

    SELECT * FROM Tissington WHERE VB LIKE 0x25b66c710920

    drop table Tissington

    --
    Michael Tissington
    Oaklodge Technologies
    http://www.oaklodge.com/technology

    "Delbert Glass" <com> wrote in message
    news:ulWia$phx.gbl... 
    >
    >[/ref]


    Michael Guest

  4. #4

    Default Re: BUG with LIKE and binary data

    Seems that if 0x20 is the last value of the binary data then it fails ... or
    am I missing something ?

    --
    Michael Tissington
    Oaklodge Technologies
    http://www.oaklodge.com/technology

    "Michael Tissington" <com> wrote in message
    news:u$phx.gbl... 
    > >
    > >[/ref]
    >
    >[/ref]


    Michael Guest

  5. #5

    Default Re: BUG with LIKE and binary data

    Although the following sounds reasonable (continued below).

    0x09 is a tab.
    0x20 is a space.
    0x25 is a percent.
    Trailing spaces get truncated or ignored (somethimes anyhow).

    Say, the values get there trailing space truncated away
    prior to checking for a match.

    Whenever you include the trailing percent in the pattern,
    the pattern end with: 'l' 'q' tab space percent.
    Which means the data must end with: 'l' 'q' tab space zero-or-characters
    to match. (am skipping the few addition previous characters
    to shorten things a little)

    The truncated data ends with: 'l' 'q' tab
    therefore there is no match
    since the data does not have the necessary space
    to fill the pattern and the row does not get selected.

    But whenever you don't include the trailing percent in the pattern,
    the pattern has a trailing space which gets truncated prior
    to checking for a match.
    Which means the data must end with: 'l' 'q' tab zero-or-characters
    Since the truncated data ends with: 'l' 'q' tab
    there is a match and the row is selected.


    (continued from above) I wonder
    if had gotten confused or if the specific
    example sometimes goes haywire.
    At time I thought neither of two queries:

    SELECT * FROM Tissington WHERE VB LIKE 0x25b66c71092025
    SELECT * FROM Tissington WHERE VB LIKE 0x25b66c710920

    selected the row but:

    SELECT * FROM Tissington WHERE VB LIKE 0x25b66c71092020

    did.

    Bye
    Delbert Glass

    "Michael Tissington" <com> wrote in message
    news:Ofb$phx.gbl... 
    or [/ref]
    this 
    > >
    > >[/ref]
    >
    >[/ref]


    Delbert Guest

Similar Threads

  1. Binary data transfer
    By HZF4004 in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: August 18th, 05:55 PM
  2. Webservices & binary data?
    By Dave in forum ASP.NET Web Services
    Replies: 1
    Last Post: November 19th, 07:14 PM
  3. Freehand Binary Data
    By Bob Juzenas in forum Macromedia Freehand
    Replies: 1
    Last Post: June 24th, 03:54 PM
  4. binary data
    By Lo Dolce Pesca in forum PHP Development
    Replies: 1
    Last Post: April 16th, 08:12 AM
  5. ASP Binary data transformation
    By Ferran Foz in forum ASP
    Replies: 1
    Last Post: October 3rd, 02:24 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