StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQuery for first result in a column - substring_index function - MySQL
text
Body
copied!<p>I can't seem to get the substring_index() to work:</p> <p>I have created a simple table as follows:</p> <pre><code>CREATE TABLE ContactList( cont_id int(11) NOT NULL AUTO_INCREMENT, last_name varchar(30), first_name varchar(20), interests varchar(100), PRIMARY KEY(cont_id)); </code></pre> <p>I then populated the ContactList table as follows:</p> <pre><code>INSERT INTO ContactList (last_name, first_name, interests) VALUES ('Murphy', 'Dave', 'Golf, Pets, Basketball'), ('Murphy', 'Ben', 'Pets, Gym, Basketball'), ('Finn', 'Belinda', 'Pets, Tennis, Knitting'), ('Murphy', 'Steve', 'Pets, Archery, Fishing'); </code></pre> <p>I ran a quick SELECT to ensure the data was entered correctly:</p> <pre><code>SELECT * FROM ContactList; </code></pre> <p>Then I ran the following query:</p> <pre><code>SELECT * FROM ContactList WHERE last_name = 'Murphy' AND SUBSTRING_INDEX(interests, ',' ,1) = 'Pets'; </code></pre> <p>I was expecting to get two records back (which I did for Ben & Steve), however, for the 'Interests' column I was assuming I should only get one interest back if it equaled 'pets' (due to the substring_index) however, I got all interests back. How can I use the SUBSTRING_INDEX() to run the query and only get the first interest listed back for each record if it says 'Pets'?</p> <p>BTW I am using MySQL Version 5.5.24 and I know the Interests would be best suited in their own table - I just want to see why substring_index is not picking the first item from the list if it equals 'pets'.</p> <p>Thanks for any input,</p> <p>Andy R ;-)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload