mysql regex & distinct values

Question

I am using mysql & perl. I have the following values for one of my columns in my database:

123 Hollywood
345 New York
847 Hollywood
192 Boston
876 Chicago

I want to select all of the distinct cities....ie, the results would be:

Hollywood
New York
Boston
Chicago

Is there a way to do this via msyql? I would imagine some sort of REGEXP but can't seem to get the logic:

SELECT * FROM table WHERE address REGEXP ''

Answer 1

Assuming that your address is always in the format <numbers><space><city>

SELECT
  DISTINCT TRIM(LEADING SUBSTRING_INDEX(address, ' ', 1) FROM address) AS city
FROM
  locations

Here's the test data I used to verify the query works. Note that I named the test table locations. My MySQL server is version 5.1.36:

--
-- Table structure for table `locations`
--

CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `address` (`address`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=6 ;

--
-- Dumping data for table `locations`
--

INSERT INTO `locations` (`id`, `address`) VALUES
(1, '123 Hollywood'),
(2, '345 New York'),
(3, '847 Hollywood'),
(4, '192 Boston'),
(5, '876 Chicago');

Here's the exact query I ran on that table:

SELECT DISTINCT TRIM(
LEADING SUBSTRING_INDEX( address, ' ', 1 )
FROM address ) AS city
FROM locations

Here's the result I got:

**city**
Hollywood
Boston
New York
Chicago