Oracle SQL concat strings

Question

I'm trying to concat 3 strings. Title, first name and last name. Either of them may be null.

I did this:

title || '.' || ' ' || fname || ' ' || lname

However, if title is null for example, I don't want the '.' and the space in front of fname.

Answer 1

try this

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;

---

result:

fullName
fname1 lname1
Mr. fname2
Ms. lname3
Mr.
lname5
fname6
[empty]
Mrs. fname8 lname8