使用case表达式来测试title是否为null并相应地连接，例如：

case
     when title IS NULL then fname || ' ' || lname 
     else title || '.' || ' ' || fname || ' ' || lname
end as my_concatenation

字符串

试试这个

WITH t
     AS (SELECT '' AS title, 'fname1' AS fname, 'lname1' AS lname FROM DUAL
         UNION ALL
         SELECT 'Mr' AS title, 'fname2' AS fname, '' AS lname FROM DUAL
         UNION ALL
         SELECT 'Ms' AS title, '' AS fname, 'lname3' AS lname FROM DUAL
         UNION ALL
         SELECT 'Mr' AS title, '' AS fname, '' AS lname FROM DUAL
         UNION ALL
         SELECT '' AS title, '' AS fname, 'lname5' AS lname FROM DUAL
         UNION ALL
         SELECT '' AS title, 'fname6' AS fname, '' AS lname FROM DUAL
         UNION ALL
         SELECT '' AS title, '' AS fname, '' AS lname FROM DUAL
         UNION ALL
         SELECT 'Mrs' AS title, 'fname8' AS fname, 'lname8' AS lname
           FROM DUAL)
SELECT TRIM (
             (CASE
                 WHEN NVL (t.title, '#na') != '#na'
                 THEN
                    t.title || '.' || ' '
                 ELSE
                    ''
              END)
          || (CASE
                 WHEN NVL (t.fname, '#na') != '#na' THEN t.fname || ' '
                 ELSE ''
              END)
          || (CASE
                 WHEN NVL (t.lname, '#na') != '#na' THEN t.lname
                 ELSE ''
              END))
          AS fullName
  FROM t;

字符串
结果：
| fullName|
| --|
| fname 1名字1|
| fname先生2|
| Ms. lname 3|
| 先生|
| lname5|
| fname6|
| [空]|
| fname夫人lname 8|