grabbing a substring of an SQL field where a string matches part of the field.

Question

Hi all and thank you for reading...

Ive been trying to find a fix to a SQL and PHP problem I have. below is a snippet of the table I am trying to work with.

The table is called web_navbar_links the field is called slug

+------------------+-------+
|       slug       | child |
+------------------+-------+
|business-multiseat|   1   |
+------------------+-------+
|consumer-multiseat|   1   |
+------------------+-------+

I have a string "multiseat" I need an SQL query that will search all the rows and pull back the first substring "business" and/or "consumer" that match the string "multiseat" and are also child='1'.

I have tried concat_ws and LIKE but dont feel they are the correct commands unless ofc I dont understand there usage :D

If anyone can help me I would be very greatful.

Dan.

Answer 1

You may want to use:

SELECT   DISTINCT SUBSTRING(slug, 1, INSTR(slug, '-') - 1) result
FROM     web_navbar_links
WHERE    SUBSTRING(slug FROM INSTR(slug, '-') + 1) = 'multiseat' AND child = 1;

Note however that this could be slow if you plan to have many rows in your web_navbar_links table. Such a query will not be able to use an index on slug if one exists.

---

Test case:

CREATE TABLE web_navbar_links (id int, slug varchar(70), child int);

INSERT INTO web_navbar_links VALUES (1, 'business-multiseat', 1);
INSERT INTO web_navbar_links VALUES (2, 'business-singleseat', 1);
INSERT INTO web_navbar_links VALUES (3, 'consumer-noseat', 1);
INSERT INTO web_navbar_links VALUES (4, 'consumer-multiseat', 1);
INSERT INTO web_navbar_links VALUES (5, 'something', 1);
INSERT INTO web_navbar_links VALUES (6, 'business-multiseat', 2);
INSERT INTO web_navbar_links VALUES (7, 'something-else', 2);

Result:

SELECT   id, SUBSTRING(slug, 1, INSTR(slug, '-') - 1) result
FROM     web_navbar_links
WHERE    SUBSTRING(slug FROM INSTR(slug, '-') + 1) = 'multiseat' AND child = 1;

+------+----------+
| id   | result   |
+------+----------+
|    1 | business |
|    4 | consumer |
+------+----------+
2 rows in set (0.00 sec)