HANA DB : How to use CONCAT syntax more than 2 fields

Question

I'm using query in HANA Studio , It's work

 CASE WHEN T0."U_XXX_SalEmp2" is not null THEN CONCAT
 (T7."SlpName",CONCAT ('+',T0."U_XXX_SalEmp2")) ELSE T7."SlpName" END
 AS"Sales Emp",

But I want to CONCAT more fields

**For Example :**  CASE WHEN T0."U_XXX_SalEmp2" is not null THEN CONCAT (T7."SlpName",CONCAT ('+',T0."U_XXX_SalEmp2"),**CONCAT
 ('+',T0."U_XXX_SalEmp3"**),**CONCAT ('+',T0."U_XXX_SalEmp4"**)) ELSE
 T7."SlpName" END  AS"Sales Emp",

score 3 · Accepted Answer · answered May 25 '20 at 10:51

3

You can use the two pipe symbols || for chained concatenation.

Your example would look like this:

CASE 
 WHEN T0."U_ISS_SalEmp2" is not null 
      THEN 
          T7."SlpName" || '+' || 
          T0."U_ISS_SalEmp2" || '+' ||
          T0."U_ISS_SalEmp3" || '+' ||
          T0."U_ISS_SalEmp4"
 ELSE
         T7."SlpName" 
END         AS "Sales Emp"

answered May 25 '20 at 10:51

Lars Br.

9,949
2
15
29

I try this but if condition is TRUE data is empty – Anubise May 25 '20 at 16:09
The concatenation (both with `CONCAT` and `||`) will return `NULL` when one of the concatenated values is `NULL`. In your case, `"U_ISS_SalEmp2"` is `NOT NULL` but one of the other values is (if you get `NULL` as the result). To avoid that, you could wrap each value in `IFNULL(, '')`. The `''` is **not** `NULL` but an empty string. With that, the `NULL`s in your concatenation will effectively be ignored. – Lars Br. May 29 '20 at 04:00
I will try this.Thank you for your advice – Anubise Jun 02 '20 at 02:34

HANA DB : How to use CONCAT syntax more than 2 fields

1 Answers1