Use LIKE + Wildcard

[JipThePeople]

MySQL version: 5.1.54-community

 

I am executing the SQL within PHP MyAdmin 3.3.8.1

 

This will successfully return records (last names beginning with 'B'):

SELECT * FROM `users` WHERE `last_name` LIKE 'B%'

 

However, this query is SUPPOSED to return records of users with last names that begin with letters A-F:

SELECT * FROM `users` WHERE `last_name` LIKE '[A-F]%'

 

Here is the table I am querying:

CREATE TABLE IF NOT EXISTS `users` (
  `userid` varchar(25) NOT NULL DEFAULT '',
  `first_name` varchar(50) NOT NULL DEFAULT '',
  `last_name` varchar(50) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `work_address1` varchar(100) NOT NULL DEFAULT '',
  `work_address2` varchar(100) DEFAULT '',
  `work_city` varchar(100) NOT NULL DEFAULT '',
  `work_state` char(2) NOT NULL DEFAULT '',
  `work_zip` varchar(10) NOT NULL DEFAULT '',
  `home_address1` varchar(100) DEFAULT '',
  `home_address2` varchar(100) DEFAULT '',
  `home_city` varchar(100) DEFAULT '',
  `home_state` char(2) DEFAULT '',
  `home_zip` varchar(10) DEFAULT '',
  `work_phone` varchar(20) NOT NULL DEFAULT '',
  `home_phone` varchar(20) DEFAULT '',
  `mobile_phone` varchar(20) DEFAULT '',
  `fax` varchar(20) DEFAULT '',
  `primary_phone` varchar(100) NOT NULL DEFAULT '',
  `manager` varchar(100) DEFAULT '',
  `connection_type` varchar(100) DEFAULT '',
  `organization` varchar(255) DEFAULT NULL,
  `group_id` int(10) unsigned DEFAULT '1',
  `must_update` int(2) unsigned NOT NULL DEFAULT '0',
  `must_change_pw` int(1) unsigned NOT NULL DEFAULT '0',
  `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `use_cached_login` int(1) unsigned NOT NULL DEFAULT '0',
  `active` int(2) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

My goal is to select users with last names that begin with a range of letters (e.g., A-F). Any help is greatly appreciated.

[JipThePeople]

Unfortunately, all I can find is

 

LIKE '[A-F]%'

 

But that doesn't work with MySQL. Has anyone else had this issue?

[AyKay47]

Not sure with this one, the SQL syntax is correct, have you tried debugging your query using mysql_error?

[JipThePeople]

Not sure with this one, the SQL syntax is correct, have you tried debugging your query using mysql_error?

 

Query does not throw an error (i.e., syntax is good), it just won't return any records and there are user names in the table with last names that begin with the range of letters.

[premiso]

You are confusing LIKE with REGEX, LIKE uses a simple wildcard, %. That is it. If you want to be more definitive in your selection, MySQL has a REGEX method. Which would be able to do what you want. If you do not have REGEX in your version of MySQL, you will be stuck with using multiple likes for each letter you want to test against (unless I am missing some unknown function to me). 

[AyKay47]

You are confusing LIKE with REGEX, LIKE uses a simple wildcard, %. That is it. If you want to be more definitive in your selection, MySQL has a REGEX method. Which would be able to do what you want. If you do not have REGEX in your version of MySQL, you will be stuck with using multiple likes for each letter you want to test against (unless I am missing some unknown function to me).

You actually can use character grouping with SQL, i'm not sure why it is not working in this case.

[JipThePeople]

Thx for the replies. I had to do it this way:

 

SELECT * FROM `users` WHERE `last_name` >='A' AND `last_name` < 'G'

 

This query returns records as expected.

[premiso]

You actually can use character grouping with SQL, i'm not sure why it is not working in this case.

 

I guess I need to do a bit more reading.