Concatenate only when data is not null in Oracle-CodePudding

I have a table with some data in my oracle table

S.ID	Name	Age
1	Adam	21
2	Mike	23
3	Daniel	null
4	Josh

Now I want to concatenate these 3 columns as a single column and my result should be as below

Details
Adam (1-21)
Mike (2-23)
Daniel (3)
Josh (4)

When there is both S.ID and age, both should be enclosed within the braces separated by a hyphen. Eg: Adam (1-21)

When the age value is null or if it doesn't have any value, only S.ID should be enclosed within the brace. There shouldn't be hyphen beside the S.ID and "null" inside the braces. Eg: Daniel (3)

I'm finding difficultly doing the same using a query in oracle.

CodePudding user response：

You may try the following

SELECT
     "Name" || ' (' || "S.ID" || CASE 
         WHEN "Age" IS NULL OR TRIM("Age")='' THEN ''
         ELSE '-' || "Age"
     END || ')' as Details
FROM tb

It uses a case expression to check for nulls and empty values for age before including it in the string.

View working demo db fiddle

Let me know if this works for you.

CodePudding user response：

The NVL2() function is handy for cases of non-null values, use it in such a way that

SELECT '('||id||NVL2(age,'-'||age,'')||')' AS "Details"
  FROM t

Details
(1-21)
(2-23)
(3)
(4)

the hyphens are prepended only for non-null age values of which both labeled null and non-labeled values are considered as null values, even as of nature for an integer type as been presumed data types for age column.

Demo