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

Is the number part of the address field, or is it a record ID in a different field? If it's the same field, this might be a bit harder. — Michael Madsen
– Michael Madsen, Commented Oct 15, 2010 at 23:21

Thomas · Accepted Answer · 2010-10-16 16:21:47Z

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

edited Oct 16, 2010 at 16:21

answered Oct 16, 2010 at 8:18

Thomas

2,1521 gold badge13 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas Over a year ago

@ginius: Do you know what version your MySQL server is? 5.1, hopefully? I've tried that exact query on a db I've set up based on what you've described. Maybe I misunderstood a detail, or maybe your MySQL version doesn't support one of the functions I used in the query.

Thomas Over a year ago

@ginius: Or is it possible that you didn't copy the query correctly? It's important that there's a space between the quotes in SUBSTRING_INDEX(address, ' ', 1).

Collectives™ on Stack Overflow

mysql regex & distinct values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related