List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 26 1999 11:22am
Subject:Re: Help with LIKE statement.
View as plain text  
>>>>> "Matthias" == Matthias Urlichs <smurf@stripped> writes:

Matthias> Hi,
Matthias> James Manning:
>> [ Sunday, September 19, 1999 ] Michael Widenius wrote:
>> > You can't do this with 'LIKE'.  Use REGEXP instead!
>> > 
>> > WHERE field REGEXP "[[:<:]]war[[:>:]]"
>> 
>> As Martin pointed out/confirmed, this isn't case-insensitive, so
>> is the only current method to use Martin's approach of
>> 
>> WHERE LOWER(field) REGEXP "[[:<:]]war[[:>:]]"
>> 
Matthias> Or REGEXP "[[:<:][Ww][Aa][Rr][:>:]]"

>> ?  it'd certainly be nice to do the /i flag (or however) that
>> things like sed/perl/etc can do :)
>> 
Matthias> Right. Or you can patch the mysqld source to do this.

Matthias> Since matches are case insensitive, I think it's a mistake to make the
Matthias> correspondign regexp searches sensitive.

Matthias> The best fix would probably be IREGEXP for case-insensitive matches.
Matthias> But then I want an option to make indices case sensitive, PLEASE...

You can make index case sensitive if you declare the column as BINARY.

A better fix:

*** /my/monty/master/mysql-3.23.3-alpha/sql/item_cmpfunc.cc	Mon Aug 30 00:03:03 1999
--- ./item_cmpfunc.cc	Sun Sep 26 14:15:50 1999
***************
*** 1063,1070 ****
  {
    if (args[0]->fix_fields(thd,tables) || args[1]->fix_fields(thd,tables))
      return 1;					/* purecov: inspected */
!   with_sum_func=args[0]->with_sum_func || args[1]->with_sum_func;;
    max_length=1; decimals=0;
    used_tables_cache=args[0]->used_tables() | args[1]->used_tables();
    const_item_cache=args[0]->const_item() && args[1]->const_item();
    if (!regex_compiled && args[1]->const_item())
--- 1063,1071 ----
  {
    if (args[0]->fix_fields(thd,tables) || args[1]->fix_fields(thd,tables))
      return 1;					/* purecov: inspected */
!   with_sum_func=args[0]->with_sum_func || args[1]->with_sum_func;
    max_length=1; decimals=0;
+   binary=args[0]->binary || args[1]->binary;
    used_tables_cache=args[0]->used_tables() | args[1]->used_tables();
    const_item_cache=args[0]->const_item() && args[1]->const_item();
    if (!regex_compiled && args[1]->const_item())
***************
*** 1078,1084 ****
        return 0;
      }
      int error;
!     if ((error=regcomp(&preg,res->c_ptr(),REG_EXTENDED | REG_NOSUB)))
      {
        (void) regerror(error,&preg,buff,sizeof(buff));
        my_printf_error(ER_REGEXP_ERROR,ER(ER_REGEXP_ERROR),MYF(0),buff);
--- 1079,1087 ----
        return 0;
      }
      int error;
!     if ((error=regcomp(&preg,res->c_ptr(),
! 		       binary ? REG_EXTENDED | REG_NOSUB :
! 		       REG_EXTENDED | REG_NOSUB | REG_ICASE)))
      {
        (void) regerror(error,&preg,buff,sizeof(buff));
        my_printf_error(ER_REGEXP_ERROR,ER(ER_REGEXP_ERROR),MYF(0),buff);
***************
*** 1123,1129 ****
  	regfree(&preg);
  	regex_compiled=0;
        }
!       if (regcomp(&preg,res2->c_ptr(),REG_EXTENDED | REG_NOSUB))
        {
  	null_value=1;
  	return 0;
--- 1126,1135 ----
  	regfree(&preg);
  	regex_compiled=0;
        }
!       if (regcomp(&preg,res2->c_ptr(),
! 		  binary ? REG_EXTENDED | REG_NOSUB :
! 		  REG_EXTENDED | REG_NOSUB | REG_ICASE))
! 
        {
  	null_value=1;
  	return 0;


----------

This will now work exactly like = and LIKE;  If either argument to
regexp is declared as binary, then the REGEXP will be case sensitive,
else it's case insensitive.

mysql> select "a" regexp "a", "a" regexp "A", "a" regexp BINARY "A";
+----------------+----------------+-----------------------+
| "a" regexp "a" | "a" regexp "A" | "a" regexp BINARY "A" |
+----------------+----------------+-----------------------+
|              1 |              1 |                     0 |
+----------------+----------------+-----------------------+
1 row in set (0.00 sec)

I will add this to 3.23.4

Regards,
Monty
Thread
Help with LIKE statement.LBFan991118 Sep
  • Re: Help with LIKE statement.James Manning18 Sep
    • Re: Help with LIKE statement.Martin Ramsch19 Sep
      • Re: Help with LIKE statement.James Manning19 Sep
        • Re: Help with LIKE statement.Martin Ramsch19 Sep
      • Re: Help with LIKE statement.Danny Carroll19 Sep
  • Help with LIKE statement.Michael Widenius19 Sep
    • Re: Help with LIKE statement.James Manning20 Sep
      • Re: Help with LIKE statement.Matthias Urlichs26 Sep
        • Re: Help with LIKE statement.Michael Widenius26 Sep
Re: Help with LIKE statement.James Manning19 Sep