List:General Discussion« Previous MessageNext Message »
From:SGreen Date:June 16 2004 8:35pm
Subject:Re: string compare
View as plain text  
I see several problems with your statement:
1) "substr" should be "substring" (see
http://dev.mysql.com/doc/mysql/en/String_functions.html)
2) "where name like '%' " means that name can have from 0 to any number of
characters. Basically it's a match on anything other than NULL. If name is
a field that accepts nulls then use "IS NOT NULL" to check for a value. Or,
if you want to check for any non-blank value you can use: name > '' .
Either of the last two options will use an index, if it exists.
3) your overall logic seems to want to compare the first 3 letters of name
to the string 'abc'. You could rewrite your entire WHERE clause as :
like 'abc%'
 and get the same results

Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


WHERE name like '


                                                                                          
                             
                      Bob Lockie                                                          
                             
                      <bjlockie@lockie.        To:       MySQL Mailing List
<mysql@stripped>                     
                      ca>                      cc:                                     
                                
                                               Fax to:                                    
                             
                      06/16/2004 04:09         Subject:  string compare                   
                             
                      PM                                                                  
                             
                                                                                          
                             
                                                                                          
                             




Where is the syntax error in this?
select name from users where name like '%' and strcmp( substr(name from
1 for 3), 'abc' )=0;

Why should I use strcmp since "MySQL automatically converts numbers to
strings as necessary, and vice versa."?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1






Thread
string compareBob Lockie16 Jun
  • Re: string comparegerald_clark16 Jun
Re: string compareSGreen16 Jun