You can use SUBSTRING_INDEX()
function for that, no need for regexes:
SUBSTRING_INDEX( SUBSTRING_INDEX(path, '/', 2), '/', -1)
Following what's already written at How to use GROUP_CONCAT in a CONCAT in mysql...
Let's assume you have a schema and sample data equivalent to:
CREATE TABLE properties
(
id integer PRIMARY KEY,
property varchar(255) NOT NULL
) ;
CREATE TABLE recommended_providers
(
id integer PRIMARY KEY,
recommended_provider_name varchar(255) NOT NULL
) ;
CREATE TABLE properties_recommended_providers
(
prop_id INTEGER NOT NULL REFERENCES properties(id),
recommended_provider_id INTEGER NOT NULL REFERENCES providers(id),
PRIMARY KEY (prop_id, recommended_provider_id)
) ;
INSERT INTO properties
VALUES (1, 'property 1'), (2, 'property 2') ;
INSERT INTO recommended_providers
VALUES (1, 'provider 1'), (2, 'provider 2'), (3, 'provider 3');
INSERT INTO properties_recommended_providers
VALUES (1, 1), (1, 2), (2, 3) ;
You can either have:
SELECT
p.id AS property_id, property,
GROUP_CONCAT(recommended_provider_name SEPARATOR ', ') AS provider_names
FROM
properties p
LEFT JOIN properties_recommended_providers pr ON pr.prop_id = p.id
LEFT JOIN recommended_providers rp ON rp.id = pr.recommended_provider_id
GROUP BY
property_id
ORDER BY
property_id ;
(Check it at SQL Fiddle)
or
SELECT
p.id AS property_id, property,
(SELECT
GROUP_CONCAT(recommended_provider_name SEPARATOR ', ')
FROM
properties_recommended_providers pr
LEFT JOIN recommended_providers rp ON rp.id =
pr.recommended_provider_id
WHERE
pr.prop_id = p.id
ORDER BY
/* You can easily order by in this case */
recommended_provider_name
) AS provider_names
FROM
properties p
ORDER BY
property_id ;
You can check the second one at SQL Fiddle
Best Answer
Test case: