Adding an Additional Except in SQL Statement

This question is not answered

I have a group that checks a field to make sure that there's no lower-case characters in a person's name. The only exception I have is at the end where the person's last name has an "Mc" in it.

EXCEPT SELECT * FROM Person
WHERE Pers_LastName like '%Mc%' COLLATE Latin1_General_CS_AI ORDER BY Pers_PersonId Pers_LastName like '%De%' COLLATE Latin1_General_CS_AI ORDER BY Pers_PersonId

I would like to include other EXCEPT statements like if the person's last name started with "De." I've tried different attempts but CRM tosses back an error each time.

What is the correct syntax for multiple except statements?

All Replies
  • Using EXCEPT in MS SQL is a lot like using a UNION, you simply write two statement, for example:

    SELECT *
    FROM Company
    WHERE comp_Deleted IS NULL


    EXCEPT


    SELECT *
    FROM Company
    WHERE comp_deleted IS NULL AND comp_name LIKE 'Magnetic%'


    So if I want to omit another company from this, I would just treat it like a standard statement, and use OR:

    SELECT *
    FROM Company
    WHERE comp_Deleted IS NULL


    EXCEPT


    SELECT *
    FROM Company
    WHERE comp_deleted IS NULL AND (comp_name LIKE 'Magnetic%' OR comp_name LIKE 'Office%')


    Worth noting though that:

    SELECT *
    FROM Company
    WHERE comp_deleted IS NULL AND (comp_name NOT LIKE 'Magnetic%' AND comp_name NOT LIKE 'Office%')

    Does the same thing in one statement. Take a look at the execution plans for both and decide which you would like to use.

    FYI, you can also do this:

    SELECT *
    FROM Company a
    LEFT JOIN (
    SELECT comp_companyId
    FROM Company
    WHERE comp_deleted IS NULL AND (comp_name LIKE 'Magnetic%' OR comp_name LIKE 'Office%')
    ) b on a.comp_companyId = b.Comp_CompanyId
    WHERE a.comp_deleted IS NULL
    AND b.comp_companyId IS NULL


    And also:

    SELECT *
    FROM Company
    WHERE comp_deleted IS NULL AND comp_companyId NOT IN
    (SELECT comp_CompanyId
    FROM Company
    WHERE comp_deleted IS NULL AND (comp_name LIKE 'Magnetic%' OR comp_name LIKE 'Office%'))

     

  • Thanks! I tried multiple combinations before coming here and I after a review and your help I realized that I had the ORDER BY twice inside the parentheses.

    EXCEPT SELECT * FROM Person

    WHERE (Pers_LastName like '%Mc%' COLLATE Latin1_General_CS_AI OR Pers_LastName like '%De%' COLLATE Latin1_General_CS_AI OR Pers_LastName like '%La%' COLLATE Latin1_General_CS_AI) ORDER BY Pers_PersonId

    It works!