[sqlite] LIKE syntax with BLOBs
I have BLOBs in my schema and the data will often start with bytes of 0 value. I'm having a tough time coming up with the proper SQL syntax to select all the columns that start with 2 0's (or any zeros). I have tried: SELECT * FROM mytable WHERE myblob LIKE 0%; SELECT * FROM mytable WHERE myblob LIKE X'0%'; SELECT * FROM mytable WHERE myblob LIKE X'0'%; and a few other combinations that use double quotes. Is there a LIKE syntax that will work with BLOBs in this way? Thank you very much. Shane
Re: [sqlite] LIKE syntax with BLOBs
Shane Baker <[EMAIL PROTECTED]> wrote: > I have BLOBs in my schema and the data will often start with bytes of 0 > value. > > I'm having a tough time coming up with the proper SQL syntax to select all > the columns that start with 2 0's (or any zeros). > > I have tried: > > SELECT * FROM mytable WHERE myblob LIKE 0%; > > SELECT * FROM mytable WHERE myblob LIKE X'0%'; > > SELECT * FROM mytable WHERE myblob LIKE X'0'%; > > and a few other combinations that use double quotes. > > Is there a LIKE syntax that will work with BLOBs in this way? Thank you > very much. > SELECT * FROM mytable WHERE myblob LIKE X'0025'; -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] LIKE syntax with BLOBs
[EMAIL PROTECTED] wrote: Shane Baker <[EMAIL PROTECTED]> wrote: I have BLOBs in my schema and the data will often start with bytes of 0 value. I'm having a tough time coming up with the proper SQL syntax to select all the columns that start with 2 0's (or any zeros). I have tried: SELECT * FROM mytable WHERE myblob LIKE 0%; SELECT * FROM mytable WHERE myblob LIKE X'0%'; SELECT * FROM mytable WHERE myblob LIKE X'0'%; and a few other combinations that use double quotes. Is there a LIKE syntax that will work with BLOBs in this way? Thank you very much. SELECT * FROM mytable WHERE myblob LIKE X'0025'; -- D. Richard Hipp <[EMAIL PROTECTED]> Richard, That's very sneaky. Of course it won't be obvious to anyone looking at the SQL why it matches all kinds of strings, people are conditioned to look for the % character. This should do the same thing in a more transparent manner, but it will take longer to execute. SELECT * FROM mytable WHERE quote(myblob) LIKE 'X''00%'; After looking at it, it may not be that much more transparent either. :-) HTH Dennis Cote