Menu
I have to concatenate 2 columns (ex. FIRSTANME and LASTNAME).
I do it this way:
FIRSTNAME || ' ' || LASTNAME`.
If one of them is null, but the other one is not null, I get null as concatenation result.
And I want following behavior
FIRSTNAME = null and LASTNAME = "Smith" ==>
FIRSTANME || ' ' || LASTNAME == ' Smith'.
How to solve this in DB2?
584
You can concatenate strings by using the CONCAT operator or the CONCAT built-in function. When the operands of two strings are concatenated, the result of the expression is a string. The operands of concatenation must be compatible strings.
The DB2 CONCAT function will combine two separate expressions to form a single string expression. You can also combine two seperate expression to form a single string expression using '||' (double pipe) notation. Concatenation: It is joining values together (by appending them to each other) to form a single long value.
A null value is a special value that Db2 interprets to mean that no data is present. If you do not specify otherwise,Db2 allows any column to contain null values. Users can create rows in the table without providing a value for the column. Using the NOT NULL clause enables you to disallow null values in the column.
4. The || is the concatenation operator in SQL.
Use coalesce
...
CONCAT( COALESCE(firstname,'') , COALESCE(lastname,'') )
Or using the || concat operator
...
COALESCE(firstname,'') || COALESCE(lastname,'')
Note that IBM recomments using the keyword concat and not the || operator.
Concat: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.sqlref%2Ffconc.htm
Coalesce: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.sqlref%2Ffcoal.htm
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
