>>>>> "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
