MySQL Substring in selection only when a character is present

locate, mysql, substring

Solution

Use this query:

select distinct 
     if(LOCATE(' - ', vendor)>0, SUBSTRING(vendor, 1, LOCATE(' - ', vendor)), vendor)
from feed_data;

Problem

A have a table that is populated by an automated process that contains several thousand company names from a third party feed. Most of the company names are just the name, but some have a hyphen after the company name. I am trying to do a select that will only return the company name. Right now I have two queries: ``` SELECT DISTINCT vendor FROM feed_data; SELECT DISTINCT SUBSTRING(vendor, 1, LOCATE(' - ', vendor)) FROM feed_data; ``` The first query gives me everything, the second query only gives me company names where there is a hyphen. I am looking for a way to get both. Sample data: ``` vendor: American Widgets Bravo Widgets - The best widgets money can buy ``` Also, this query is actually a subquery part of a larger query that is populating a menu, so that may limit the possible solutions.

Original source