删除空格后选择唯一数据

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.

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.

I think this maybe what you are looking for?

SELECT registration FROM customers WHERE customer_id in
  (SELECT registration AS installs_registration FROM installs WHERE customers.customer_id = installs.id,
  SELECT registration AS services_registration FROM services  WHERE customers.customer_id = services.id,
  SELECT registration AS deinstalls_registration FROM deinstalls WHERE customers.customer_id = deinstalls.id)

You have two options. The first is to add DISTINCT to the first REPLACE on registration:

SELECT DISTINCT REPLACE(registration, ' ', '') FROM (
  ... your UNION queries
)
AS vehicles

The second is to use REPLACE in each of your subqueries; the UNION ensures that you'll get distinct results:

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