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.

score 5 · Accepted Answer · answered Jun 01 '12 at 14:19

5

Use this query:

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

answered Jun 01 '12 at 14:19

anubhava

761,203
64
569
643

Perfect! Simple, straightforward and works like a charm! Thanks! – Wige Jun 01 '12 at 14:30

score 1 · Answer 2 · answered Jun 01 '12 at 14:16

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;

Rahul · Answer 3 · 2012-06-01T14:32:51.863

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;

MySQL Substring in selection only when a character is present

3 Answers3