删除空格后选择唯一数据

Question

I have the following MySQL statement:

SELECT registration FROM
  (SELECT registration FROM installs
      WHERE customer_id = :id1
  UNION SELECT registration FROM services
      WHERE customer_id = :id2
  UNION SELECT registration FROM deinstalls
      WHERE customer_id = :id3)
AS vehicles

I need to select unique "registration"'s from multiple tables which this statement does almost perfectly.

It selects AB01 CDE and aB01 cdE as one item, however AB01CDE / aB01cdE are selected as a different item.

I have tried using REPLACE around the first registration to remove spaces when selecting unique items.

Answer 1

REPLACE() should work. Did you do this?

SELECT DISTINCT REPLACE(registration, ' ', '') as Registration
FROM (SELECT registration
      FROM installs
      WHERE customer_id = :id1
      UNION ALL
      SELECT registration
      FROM services
      WHERE customer_id = :id2
      SELECT registration
      FROM deinstalls
      WHERE customer_id = :id3
     ) v;

It is also possible that the separating characters are not spaces, in which case this will not work.