Problem with LIKE/REGEXP
Hello everyone! I have a table with a MEDIUMBLOB column: CREATE TABLE mytab ( oid INTEGER ... odata MEDIUMBLOB ); in the mediumblob field i store an encoded string (bytes). I would like to perform regular expression searches on this field here is an example that works (produces matches): SELECT oid FROM mytab WHERE odata LIKE "[EMAIL PROTECTED]"\ngmosx%"; here is an example that doesnt work (no matches returned): SELECT oid FROM mytab WHERE odata LIKE "[EMAIL PROTECTED]"\017athens1234%"; the \017 character seems to be the problem since the following produces matches: SELECT oid FROM mytab WHERE odata LIKE "[EMAIL PROTECTED]"_athens1234%"; I tried using a MEDIUMTEXT and/or REGEXP but without success! Any idea how to do string matches on string-encoded binary data? Thanks in advance for any info! George Moschovitis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with LIKE/REGEXP
Hi, What db type do u use? If I'm right InnoDB doesn't support Free text search... bye Hans At 14:29 5-11-03, you wrote: Hello everyone! I have a table with a MEDIUMBLOB column: CREATE TABLE mytab ( oid INTEGER ... odata MEDIUMBLOB ); in the mediumblob field i store an encoded string (bytes). I would like to perform regular expression searches on this field here is an example that works (produces matches): SELECT oid FROM mytab WHERE odata LIKE "[EMAIL PROTECTED]"\ngmosx%"; here is an example that doesnt work (no matches returned): SELECT oid FROM mytab WHERE odata LIKE "[EMAIL PROTECTED]"\017athens1234%"; the \017 character seems to be the problem since the following produces matches: SELECT oid FROM mytab WHERE odata LIKE "[EMAIL PROTECTED]"_athens1234%"; I tried using a MEDIUMTEXT and/or REGEXP but without success! Any idea how to do string matches on string-encoded binary data? Thanks in advance for any info! George Moschovitis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with LIKE/REGEXP
Here is some additional information: I tried with version 3.23 and version 4.0 And I am using MyISAM tables. This is not a full text search an there is no index on the column. any ideas? George Moschovitis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with LIKE/REGEXP
Hi George, What are you actually trying to match with \017? As far as I know, it's treating the \0 part as a NUL byte and trying to match that. Are you trying to match a NUL byte? Or are you trying to match ASCII 17 or something? http://www.mysql.com/doc/en/String_syntax.html Matt - Original Message - From: "George Moschovitis" To: <[EMAIL PROTECTED]> Sent: Wednesday, November 05, 2003 7:29 AM Subject: Problem with LIKE/REGEXP > Hello everyone! > > I have a table with a MEDIUMBLOB column: > > CREATE TABLE mytab ( > oid INTEGER ... > odata MEDIUMBLOB > ); > > in the mediumblob field i store an encoded string (bytes). > I would like to perform regular expression searches on this field > > here is an example that works (produces matches): > > SELECT oid FROM mytab WHERE odata LIKE "[EMAIL PROTECTED]"\ngmosx%"; > > here is an example that doesnt work (no matches returned): > > SELECT oid FROM mytab WHERE odata LIKE "[EMAIL PROTECTED]"\017athens1234%"; > > the \017 character seems to be the problem since the following produces > matches: > > SELECT oid FROM mytab WHERE odata LIKE "[EMAIL PROTECTED]"_athens1234%"; > > I tried using a MEDIUMTEXT and/or REGEXP but without success! > > Any idea how to do string matches on string-encoded binary data? > > Thanks in advance for any info! > > George Moschovitis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with LIKE/REGEXP
> What are you actually trying to match with \017? As far as I know, it's > treating the \0 part as a NUL byte and trying to match that. Are you > trying to match a NUL byte? Or are you trying to match ASCII 17 or > something? Hello matt, i am trying to match a binary string (with binary data) against a column of type MEDIUMTEXT/MEDIUMBLOB that contains binary data... how can i encode the byte 17 into a mysql string? is it possible? -g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with LIKE/REGEXP
Hi George,
- Original Message -
From: "George Moschovitis"
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 06, 2003 9:03 AM
Subject: RE: Problem with LIKE/REGEXP
> > What are you actually trying to match with \017? As far as I know,
> it's
> > treating the \0 part as a NUL byte and trying to match that. Are you
> > trying to match a NUL byte? Or are you trying to match ASCII 17 or
> > something?
>
> Hello matt,
>
> i am trying to match a binary string (with binary data) against a
column of type
> MEDIUMTEXT/MEDIUMBLOB that contains binary data...
I know. :-) I didn't know, though, if you wanted to match a NUL byte,
the literal string "\017", ASCII 17, or what.
> how can i encode the byte 17 into a mysql string? is it possible?
Can't you just put it in the string like any character? Or does it cause
a problem?
I guess you could use the CHAR() function otherwise?
... WHERE column LIKE CONCAT('%stuff', CHAR(17), 'stuff%');
I think!
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
