I have a table with 15 fields, 4 of which are named FIRST, MIDDLE, LAST, and STATE. I want to create calculated columns which will combine these 4 fields together in various combinations such as FIRSTLASTSTATE or FIRSTMIDDLELAST. The below code works excellently when there are no null values in each of the record's fields. However when any of the fields are null, the entire cell returns a null. How can I ignore the null and just combine the fields that are not null?
select * from EN_MEMBERS
alter table EN_MEMBERS
FLS as rtrim(FIRST)+rtrim(LAST)+rtrim(STATE)