By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,806 Members | 1,089 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,806 IT Pros & Developers. It's quick & easy.

Multiple LIKE comparison using IN operator

P: n/a
Is there anyway to compare a field with multiple string values using
LIKE?

Here's the statement I have now:
SELECT * FROM list
WHERE email LIKE CONVERT( _utf8 'h******@hotmail.com' USING latin1 )
OR email LIKE CONVERT( _utf8 'j***@hotmail.com' USING latin1 )";

I know how to do it using the IN operator,
SELECT * FROM list
WHERE email IN ('h******@hotmail.com', 'j***@hotmail.com')

but I need to be able to use the LIKE operator with the CONVERT
function. The reason I am asking is because I am trying to match 50+
different email addresses.

Anyway how to do this?

TIA
Oct 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Evil Bert wrote:
Is there anyway to compare a field with multiple string values using
LIKE?

Here's the statement I have now:
SELECT * FROM list
WHERE email LIKE CONVERT( _utf8 'h******@hotmail.com' USING latin1 )
OR email LIKE CONVERT( _utf8 'j***@hotmail.com' USING latin1 )";

I know how to do it using the IN operator,
SELECT * FROM list
WHERE email IN ('h******@hotmail.com', 'j***@hotmail.com')

but I need to be able to use the LIKE operator with the CONVERT
function. The reason I am asking is because I am trying to match 50+
different email addresses.

Anyway how to do this?


I don't understand why you need to use LIKE, since you aren't using any
wildcards in your examples above. So the usage of LIKE is simply
testing for string equality. You could replace the instances of LIKE
with the = sign and it work the same, as far as I know.

Anyway, no, the IN operator does strict equality comparisons, it has no
support for wildcards.

However, if you did need to do comparisons with wildcards against many
patterns, I would suggest loading the patterns into a temporary table,
joining your `list` table to that temp table, and then using LIKE in the
join condition.

Regards,
Bill K.
Oct 21 '05 #2

P: n/a
Doesn't the LIKE compare the string regardless of upper or lower case.
When using = the exact case is searched for. Correct me if I am wrong.

Thanks

On Thu, 20 Oct 2005 19:18:40 -0700, Bill Karwin <bi**@karwin.com>
wrote:
Evil Bert wrote:
Is there anyway to compare a field with multiple string values using
LIKE?

Here's the statement I have now:
SELECT * FROM list
WHERE email LIKE CONVERT( _utf8 'h******@hotmail.com' USING latin1 )
OR email LIKE CONVERT( _utf8 'j***@hotmail.com' USING latin1 )";

I know how to do it using the IN operator,
SELECT * FROM list
WHERE email IN ('h******@hotmail.com', 'j***@hotmail.com')

but I need to be able to use the LIKE operator with the CONVERT
function. The reason I am asking is because I am trying to match 50+
different email addresses.

Anyway how to do this?


I don't understand why you need to use LIKE, since you aren't using any
wildcards in your examples above. So the usage of LIKE is simply
testing for string equality. You could replace the instances of LIKE
with the = sign and it work the same, as far as I know.

Anyway, no, the IN operator does strict equality comparisons, it has no
support for wildcards.

However, if you did need to do comparisons with wildcards against many
patterns, I would suggest loading the patterns into a temporary table,
joining your `list` table to that temp table, and then using LIKE in the
join condition.

Regards,
Bill K.


Oct 21 '05 #3

P: n/a
Evil Bert wrote:
Doesn't the LIKE compare the string regardless of upper or lower case.
When using = the exact case is searched for. Correct me if I am wrong.


All string comparisons in MySQL are case-insensitive, unless by use of
the BINARY keyword they are made to be case-sensitive.

Try executing the following expressions:

SELECT 'abc' LIKE 'abc';
SELECT 'abc' LIKE 'ABC';
SELECT 'abc' LIKE BINARY 'abc';
SELECT 'abc' LIKE BINARY 'ABC';
SELECT 'abc' = 'abc';
SELECT 'abc' = 'ABC';
SELECT 'abc' = BINARY 'abc';
SELECT 'abc' = BINARY 'ABC';
SELECT 'abc' IN ('abc');
SELECT 'abc' IN ('ABC');
SELECT 'abc' IN (BINARY 'abc');
SELECT 'abc' IN (BINARY 'ABC');

See
http://dev.mysql.com/doc/refman/5.0/...functions.html
for a reference.

Regards,
Bill K.
Oct 21 '05 #4

P: n/a
Thanks Bill, I just learned something new. I just thought the = equal
sign did an exact match. I guess it's from using PHP so much that I
thought this way...

Thanks again.
On Thu, 20 Oct 2005 21:28:54 -0700, Bill Karwin <bi**@karwin.com>
wrote:
Evil Bert wrote:
Doesn't the LIKE compare the string regardless of upper or lower case.
When using = the exact case is searched for. Correct me if I am wrong.


All string comparisons in MySQL are case-insensitive, unless by use of
the BINARY keyword they are made to be case-sensitive.

Try executing the following expressions:

SELECT 'abc' LIKE 'abc';
SELECT 'abc' LIKE 'ABC';
SELECT 'abc' LIKE BINARY 'abc';
SELECT 'abc' LIKE BINARY 'ABC';
SELECT 'abc' = 'abc';
SELECT 'abc' = 'ABC';
SELECT 'abc' = BINARY 'abc';
SELECT 'abc' = BINARY 'ABC';
SELECT 'abc' IN ('abc');
SELECT 'abc' IN ('ABC');
SELECT 'abc' IN (BINARY 'abc');
SELECT 'abc' IN (BINARY 'ABC');

See
http://dev.mysql.com/doc/refman/5.0/...functions.html
for a reference.

Regards,
Bill K.


Oct 24 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.