SELECT (Firstname || '-' || Middlename || '-' || Surname)  AS example_column
FROM example_table

John--Smith
Jane-Anne-Smith

SELECT firstname || COALESCE('-' || middlename, '') || '-' || surname ...

SELECT first name || DECODE(middlename, NULL, '', '-' || middlename) || '-' || surname...

SELECT concat_ws('-', Firstname, Middlename, Surname)  AS example_column
FROM example_table

SELECT concat_ws('-', Firstname, nullif(trim(Middlename), ''), Surname)  AS example_column
 FROM example_table

SELECT FirstName || '-' || ISNULL(MiddleName + '-', '') || Surname

select first_name || coalesce('-' || middle_name, '') || '-' || last_name
from t;

|        ?column? |
|-----------------|
|      john-smith |
| jane-anne-smith |

select first_name || coalesce('-' || nullif(middle_name,'') , '') || '-' || last_name
from t;

|        ?column? |
|-----------------|
|      john-smith |
|      obi-kinobi |
| jane-anne-smith |

select case Middlename is not null then (Firstname || '-' || Middlename || '-' || Surname)
    else (Firstname || '-' || Surname) end AS example_column
from ....

select Firstname
      || case when Middlename <> '' Then '-'||Middlename  else '' END
      || case when Surname<> '' Then '-'||Surname else '' END

SELECT Firstname || VALUE( '-' || NULLIF('Middlename',''),'') || '-' || Surname'
       AS example_column
FROM example_table

SELECT
   REPLACE(Firstname || '-' || Middlename || '-' || Surname,'--','-')
   AS example_column
FROM example_table;

SELECT (Firstname || '-' || Middlename || '-' || Surname)  AS example_column
FROM example_table

-- PostgreSQL/SQLite
 SELECT 'sth' || NULL
 -- NULL


 -- Oracle
 SELECT 'sth' || NULL
 -- sth