* tag
> I need to do a select query that can do the following:
> select * from table where col1 like hex("somestring");
This was a bit confusing... :)
hex("somestring") will always return 0, unless the string is a numerical
value:
mysql> select hex('65'),hex(65),hex("A");
+-----------+---------+----------+
| hex('65') | hex(65) | hex("A") |
+-----------+---------+----------+
| 41 | 41 | 0 |
+-----------+---------+----------+
1 row in set (0.00 sec)
> My problem is HOW do I get the % in there???
I think you should not use the hex() function in this case.
> The Mysql Server is 4.0.4 and the table has a blob field with hex
> stored in it ....
I doubt if this is the case... hex is a representation, not a format. If you
really have hex strings stored in the table, you wouldn't need a blob,
because all characters are ascii (0-9 + a-f).
You probably have binary data stored in your blob, and you can view it using
hex representation, but you must search on the binary values, not on the hex
representation. Something like this:
SELECT * FROM table WHERE col1 LIKE "x#2\\1\_@!\"æøå\0%";
You must escape the characters ", ', \ and ascii 0 with a preceeding \.
(This also aplies to % and _ when you want to search for them using LIKE).
In other words, the above string is really: x#2\1_@!"æøå + ascii 0 + %.
Languages supporting mysql have a special funtion for this, called
mysql_escape_string() in the C API, quote() in perl DBI.
HTH,
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
