MySQL Substring in selection only when a character is present

Question

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.

Answer 1

Use this query:

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

Answer 2

Use a union - one query for each half:

SELECT DISTINCT 
    SUBSTRING(vendor, 1, LOCATE(' - ', vendor))
FROM feed_data
where vendor like '%-%'
union
SELECT DISTINCT
    vendor
FROM feed_data
where vendor not like '%-%';

or, use if():

SELECT DISTINCT
    if (vendor like '%-%', SUBSTRING(vendor, 1, LOCATE(' - ', vendor)), vendor)
FROM feed_data;

or, use case:

SELECT DISTINCT
    case
        when vendor like '%-%' then SUBSTRING(vendor, 1, LOCATE(' - ', vendor))
        else vendor
    end
FROM feed_data;

Answer 3

Something like this may be (It will give you all the vendor with a added field to mention whether it's with hyphen or not)

SELECT distinct vendor,
(case when LOCATE('-', vendor) >= 0 
then 'True'
else 'False' end) as IsVendorWithHiphen
FROM feed_data;

This One will give you the result of all vendor with - replaced wherever

select distinct 
(case when LOCATE('-', vendor) >= 0 then SUBSTRING(vendor, 1, LOCATE(' - ', vendor))
 else vendor) as myvendor from feed_data;