SQL - Cleaning database from wrong First Name / Last Name

Question

I've got table where we store First Name ,Last Name and Company Name etc.

`customers` (
    `Company Name` VARCHAR(100) NOT NULL , 
    `First Name` VARCHAR(100) NOT NULL , 
    `Last Name` VARCHAR(100) NOT NULL 
) 

It contatins bad data , like :

Company Name | First Name | Last Name
--------------------------------------
Why Asking   | bbbToday   | cxzNot
Temp         | CCCttt     | xcy 
Blank        | John       | Travolta
Windows      | Johny      | Bravo

In second table I've got filter which I should use to clean the table :

`filter` (
    `operator` VARCHAR(100) NOT NULL , 
    `value` VARCHAR(100) NOT NULL 
) 

operator   | value
------------------------
equal      | cxz
starts with| xcy
contains   | CCC

I want to select all data without bad data like :

Company Name | First Name | Last Name
--------------------------------------
Blank        | John       | Travolta
Windows      | Johny      | Bravo

I just try

select * from customers where not exists (select value from filter)

---

And it's ok , but it only works when value exist/not exist ,but the problem is how I can check if for Example First Name starts with "xcy" or "CCC" ?

Answer 1

The following query should do what you need. You need to manually adapt the condition according to the content of the filter table :

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM filter f
    WHERE 
        ( f.operator = 'equal' AND (f.value = c.FirstName OR f.value = c.LastName))
        OR ( f.operator = 'starts with' AND (c.FirstName LIKE CONCAT(f.value, '%') OR c.LastName LIKE CONCAT(f.value, '%')))
        OR ( f.operator = 'contains' AND (c.FirstName LIKE CONCAT('%', f.value, '%') OR c.LastName LIKE CONCAT('%', f.value, '%')))
)