Search code examples
sqlexcelodbcmicrosoft-query

Query WHERE Only Alphabetic Characters


I am trying to filter out data in my Excel sheet of customers for my company. The three fields I need to by are FIRST_NAME, LAST_NAME, and COMPANY_NAME.

The rules are as follows:

  • FIRST_NAME AND LAST_NAME must NOT be NULL
  • FIRST_NAME AND LAST_NAME must be only alphabetic
  • The above rules are irrelevant IF COMPANY_NAME is NOT NULL

So, just to reiterate to be clear.. A customer must have a FIRST_NAME AND a LAST_NAME (They cannot be missing one or both), BUT, if they have a COMPANY_NAME they are allowed to not have a FIRST_NAME and/or LAST_NAME.

Here's some example data and if they should stay in the data or not:

FIRST_NAME | LAST_NAME | COMPANY_NAME |         Good customer?
-----------|-----------|--------------|--------------------------------
   Alex    |  Goodman  |    AG Inc.   | Yes - All are filled out
   John    |  Awesome  |              | Yes - First and last are fine
   Cindy   |           |  Cindy Corp. | Yes - Company is filled out
           |           |   Blank Spa  | Yes - Company is filled out
           |           |              | No - Nothing is filled out
  Gordon   |  Mang#2   |              | No - Last contains non-alphabet
  Jesse#5  |  Levvitt  |    JL Inc.   | Yes - Company is filled out
  Holly    |           |              | No - No last or company names

Here is the query (With some fields in the SELECT clause removed):

SELECT VR_CUSTOMERS.CUSTOMER_ID, VR_CUSTOMERS.FIRST_NAME, VR_CUSTOMERS.LAST_NAME, VR_CUSTOMERS.COMPANY_NAME, ...
FROM DEV.VR_CUSTOMERS VR_CUSTOMERS
WHERE (
LENGTH(NAME)>4 AND
(UPPER(NAME) NOT LIKE UPPER('%delete%')) AND
(COMPANY_NAME IS NOT NULL OR (COMPANY_NAME IS NULL AND FIRST_NAME IS NOT NULL AND LAST_NAME IS NOT NULL AND FIRST_NAME LIKE '%^[A-z]+$%' AND LAST_NAME LIKE '%^[A-z]+$%'))
)

I've tried as well the regex of '%[^a-z]%'. I've tried RLIKE and REGEXP, instead of LIKE, and those did not seem to work either.

With the above query, the results only show records with a COMPANY_NAME.


Solution

  • Fixed the issue using REGEXP_LIKE and the regex ^[A-z]+$.

    Here is the WHERE clause after this fix:

    WHERE (
    LENGTH(NAME)>4 AND
    (UPPER(NAME) NOT LIKE UPPER('%delete%')) AND
    (COMPANY_NAME IS NOT NULL OR (COMPANY_NAME IS NULL AND REGEXP_LIKE(FIRST_NAME, '^[A-z]+$') AND REGEXP_LIKE(LAST_NAME, '^[A-z]+$')))
    )