Kaan Burak Sener Kaan Burak Sener - 4 months ago 12
MySQL Question

MYSQL - Whitespace issue Group By

I am performing

UNION
while getting the same type of information (company_name, vat, and email) from two different tables (adv and pub). The SQL query is below:

SELECT TRIM(UPPER(company_name)), vat, company_owner_email FROM (
(SELECT company_name, vat, admin.email as company_owner_email FROM pub
LEFT JOIN admin ON pub.manager = admin.id
WHERE company_name > '')
UNION
(SELECT company_name, vat, admin.email as company_owner_email FROM adv
LEFT JOIN admin ON adv.manager = admin.id
WHERE company_name > '')
) company
GROUP BY company_name
ORDER BY company_name;


However, when I run this query, I am getting some rows with the same company_name and when I try to copy them in order to understand why they are not filtered, I am facing the following result.


  • '1DNAF SARL', '123456', NULL

  • '1DNAF SARL         ', '', NULL

    Even if I am using
    TRIM()
    function, it somehow doesn't work and as a result,
    GROUP BY
    doesn't consider these records identical and doesn't eliminate one of them. How should I solve this issue?



By the way, when I have tried to TRIM() the company_name field separately, it worked in both but to join the results doesn't work properly.

Answer Source

It is better to normalize the company name different ways in SELECT (how it will be displayed) and GROUP (how it will be grouped):

Consider that there might be cases with 2 spaces in the middle or TAB SPACE TAB in the end of the string. So I'd rather use replace instead of TRIM().

SELECT UPPER(company_name), vat, company_owner_email FROM (
    (SELECT company_name, vat, admin.email as company_owner_email FROM pub
        LEFT JOIN admin ON pub.manager = admin.id
        WHERE company_name > '')
        UNION
    (SELECT company_name, vat, admin.email as company_owner_email FROM adv
        LEFT JOIN admin ON adv.manager = admin.id
        WHERE company_name > '')
    ) company
GROUP BY replace(replace(replace(replace(company_name,'\t',''), ' ', ''), '.', ''), ',','')
ORDER BY company_name;