How to create SQL Query that concatenates and parses value in result set

How to make an SQL query for a table based on the following conditions:

Result is a single column that concatenates all fields delimited by a dash into a single string (ex: FieldA-FieldB-FieldC-FieldD-FieldE)
If a given field is NULL or if the field's value is a string such as "EMPTY" or "NA", do not concatenate that field's value into the result string

Example Table Person (FirstName, LastName, Street, City, State):

Bob | Dylan | 555 Street | Mountain View | California
Ally | M | NULL | Seattle | Washington
Jan | Van | EMPTY | EMPTY | Oregon
Nancy | Finn | EMPTY | EMPTY | NA
Don | William | NULL | EMPTY | Illinois

Result:

Bob-Dylan-555 Street-Mountain View-California
Ally-M-Seattle-Washington
Jan-Van-Oregon
Nancy-Finn
Don-William-Illinois

I know this can be done programatically, but wanted to know if this can be done in SQL and if it would be more efficient to do so in the query itself.

Solution

Fully-baked solution for SQL Server 2017 and above:

SELECT *
FROM Person p
OUTER APPLY (
   SELECT STRING_AGG(NULLIF(NULLIF(val, 'EMPTY'), 'NA'), '-') 
   WITHIN GROUP (ORDER BY n) AS val
   FROM (VALUES (1, p.FirstName), (2, p.LastName),(3, p.Street),
                (4,p.City), (5, p.State)) z(n, val)
 )sub;

DBFiddle Demo

MySQL version using CONCAT_WS:

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

SELECT CONCAT_WS('-',
  NULLIF(NULLIF(FirstName, 'EMPTY'), 'NA'),
  NULLIF(NULLIF(LastName, 'EMPTY'), 'NA'),
  NULLIF(NULLIF(Street, 'EMPTY'), 'NA'),
  NULLIF(NULLIF(City, 'EMPTY'), 'NA'),
  NULLIF(NULLIF(State, 'EMPTY'), 'NA')) AS r
FROM Person p;

DBFiddle Demo2