Re: Need help on WHERE ... LIKE Query
This should work for you: SELECT * FROM sometable WHERE surname BETWEEN 'A' AND 'D' ORDER BY surname In my quick test the first parameter is inclusive while the second is not, which is why it is D and not C. On Thursday, October 30, 2003, at 02:22 PM, Scott Brown wrote: I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on WHERE ... LIKE Query
You can use RLIKE which is regular expressions then you should be able to execute SELECT * FROM sometable WHERE surname RLIKE '^[A-C]' ORDER BY surname; Kelley Scott Brown wrote: Hi, List, I looked here: http://www.mysql.com/doc/en/String_comparison_functions.html But I am not seeing what I need. I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; This works in another RDBMS. It doesn't return a syntax error, either, but it returns no records. My guess is that MySQL is interpreting the whole thing literally, rather than looking for what I want. I need this to return all records where surname begins with the letters A through C (that is, all records with a surname which begins with A, B, or C). Anybody got a how-to? I'm sure there must be some way, other than to do this three times. Some of these can vary; that is, it may be 0-9, or 0-Z (show all), even, so I don't want to do a bunch of OR'ing, either. Thanks! --Scott Brown -- 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: Need help on WHERE ... LIKE Query
Thanks for all of the responses! Actually, Brent Baisley wins the syntax question of the day. The BETWEEN syntax is what I needed. REGEXP and RLIKE do not return any records, they return a count of the number of rows matching the expression. Thanks! --Scott Brown At 11:22 AM 10/30/2003, you wrote: Hi, List, I looked here: http://www.mysql.com/doc/en/String_comparison_functions.html But I am not seeing what I need. I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; This works in another RDBMS. It doesn't return a syntax error, either, but it returns no records. My guess is that MySQL is interpreting the whole thing literally, rather than looking for what I want. I need this to return all records where surname begins with the letters A through C (that is, all records with a surname which begins with A, B, or C). Anybody got a how-to? I'm sure there must be some way, other than to do this three times. Some of these can vary; that is, it may be 0-9, or 0-Z (show all), even, so I don't want to do a bunch of OR'ing, either. Thanks! --Scott Brown -- 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: Need help on WHERE ... LIKE Query
Thanks so much Brent, this is what I was looking for. However, what do I do when I get to 'Z'? I looked here, and now I am really confused: http://www.mysql.com/doc/en/Comparison_Operators.html It seems to say that BETWEEN returns a rowcount as well? I am guessing that these all return records... So, what should I use, RLIKE, BETWEEN or??? Thanks, --Scott At 11:35 AM 10/30/2003, you wrote: This should work for you: SELECT * FROM sometable WHERE surname BETWEEN 'A' AND 'D' ORDER BY surname In my quick test the first parameter is inclusive while the second is not, which is why it is D and not C. On Thursday, October 30, 2003, at 02:22 PM, Scott Brown wrote: I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: Need help on WHERE ... LIKE Query
Oh, well, chalk it up to experience. RLIKE is what works the way I want. DOH! Thanks, --Scott Brown At 12:34 PM 10/30/2003, you wrote: Thanks so much Brent, this is what I was looking for. However, what do I do when I get to 'Z'? I looked here, and now I am really confused: http://www.mysql.com/doc/en/Comparison_Operators.html It seems to say that BETWEEN returns a rowcount as well? I am guessing that these all return records... So, what should I use, RLIKE, BETWEEN or??? Thanks, --Scott At 11:35 AM 10/30/2003, you wrote: This should work for you: SELECT * FROM sometable WHERE surname BETWEEN 'A' AND 'D' ORDER BY surname In my quick test the first parameter is inclusive while the second is not, which is why it is D and not C. On Thursday, October 30, 2003, at 02:22 PM, Scott Brown wrote: I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on WHERE ... LIKE Query
The BETWEEN operator works like and greater and less than search. So, you can do the exact same query like this: SELECT * FROM sometable WHERE surname='A' AND surname'D' MySQL may actually optimize them the same way, but using BETWEEN is more readable. To include 'Z', just do a greater than search for what you want: SELECT * FROM sometable WHERE surname='X' The reason why you would want to do it this way instead of using RLIKE is that MySQL will use an index much more efficiently. Do both queries with and explain in front of it. You'll see how MySQL performed the BETWEEN search, or greater/less than, much more efficiently. If you have an index on surname that is. On Thursday, October 30, 2003, at 03:34 PM, Scott Brown wrote: Thanks so much Brent, this is what I was looking for. However, what do I do when I get to 'Z'? I looked here, and now I am really confused: http://www.mysql.com/doc/en/Comparison_Operators.html It seems to say that BETWEEN returns a rowcount as well? I am guessing that these all return records... So, what should I use, RLIKE, BETWEEN or??? Thanks, --Scott -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on WHERE ... LIKE Query
Hi, No, col BETWEEN 'A' AND 'D' is not the same as col = 'A' AND col 'D'. BETWEEN is equivalent to col = 'A' AND col = 'D'. One will include cols that equal 'D' and the other won't. :-) Matt - Original Message - From: Brent Baisley Sent: Thursday, October 30, 2003 3:12 PM Subject: Re: Need help on WHERE ... LIKE Query The BETWEEN operator works like and greater and less than search. So, you can do the exact same query like this: SELECT * FROM sometable WHERE surname='A' AND surname'D' MySQL may actually optimize them the same way, but using BETWEEN is more readable. To include 'Z', just do a greater than search for what you want: SELECT * FROM sometable WHERE surname='X' The reason why you would want to do it this way instead of using RLIKE is that MySQL will use an index much more efficiently. Do both queries with and explain in front of it. You'll see how MySQL performed the BETWEEN search, or greater/less than, much more efficiently. If you have an index on surname that is. On Thursday, October 30, 2003, at 03:34 PM, Scott Brown wrote: Thanks so much Brent, this is what I was looking for. However, what do I do when I get to 'Z'? I looked here, and now I am really confused: http://www.mysql.com/doc/en/Comparison_Operators.html It seems to say that BETWEEN returns a rowcount as well? I am guessing that these all return records... So, what should I use, RLIKE, BETWEEN or??? Thanks, --Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
