Multiple LIKE comparison using IN operator

[Evil Bert]

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

[Bill Karwin]

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.

[Evil Bert]

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.

[Bill Karwin]

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.

[Neeper]

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.