[sqlite] LIKE syntax with BLOBs

[Shane Baker]

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

[drh]

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

[Dennis Cote]

[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