Returning the original value if it doesn't mat

Question

I have a line on my SQL export field, and I am trying to remove return a blank value in a field if it exactly matches "AMA" in the last name. However, when I export the file with the current code, and names without "AMA" as the last name are fully blanked out.

,Max(
    Orders.ShipFirstName
    + ' ' +  
        CASE Orders.ShipLastName
            WHEN 'AMA' THEN ''
        END
) As ShipFullName

Right now, if someone is "Ray Miller", ShipFullName will be returned as blank. But if their name is "Ray AMA", then their name is returned as "Ray". I want it so "Ray Miller" is returned when AMA is not found in the last name field.

Answer 1

To avoid having a trailing blank, I would do this:

max(
    case 
        when Orders.ShipLastName = 'AMA' then Orders.ShipFirstName
        else Orders.ShipFirstName + ' ' + Orders.ShipLastName
    end
) as ShipFullName

Answer 2

,Max(
    Orders.ShipFirstName
    + ' ' +  IF(Orders.ShipLastName = 'AMA', '', Orders.ShipLastName)
) As ShipFullName

Answer 3

CASE Orders.ShipLastName
    WHEN 'AMA' THEN ''
    ELSE Orders.ShipLastName
END