concat(customers.firstname, ' ', customers.lastname)

TRIM(CONCAT(customers.firstname, ' ', customers.lastname))

TRIM([LEADING | TRAILING | BOTH] [characters] FROM YourColumnOrString)

case
  when customers.lastname = ''
    then customers.firstname
  else
    concat(customers.firstname, ' ', customers.lastname)
end

trim(both from COALESCE(firstname, '') || ' ' || COALESCE(lastname, '')

SELECT RTRIM(CONCAT(LTRIM(RTRIM(First_Name)) , ' ' , LTRIM(RTRIM(Middle_Name))))
AS 'Full_Name'
FROM tableName;

SELECT (RTRIM(LTRIM(RTRIM(First_Name)) + ' ' + LTRIM(RTRIM(Middle_Name))) + ' ' + LTRIM(RTRIM(Last_Name)))
AS 'Full_Name'
FROM tableName;