Details
-
Improvement
-
Status: Closed
-
Minor
-
Resolution: Fixed
-
1.34.0
Description
This function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner. It separates those concatenated string values with the delimiter specified in the first function argument. It has the following syntax:
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
Unlike CONCAT function, CONCAT_WS ignores NULL value, for example:
select concat_ws(',', 'a', 'b', null, null, 'c'); -- result in 'a,b,c'
One important thing to be noted is that CONCAT_WS has two NULL semantics for the separator argument in different Database products(So we need two function in Calcite to represent).
Taking the following SQL as an example:
select concat_ws(null, 'a', 'b', null, null, 'c');
- MSSQL: never returns null and treats null as empty string, so the above SQL results in "abc" (For more details, see mssql-concat_ws)
- MySQL and Postgresql: returns null when the separator is null, so the example SQL returns NULL.
Attachments
Issue Links
- is related to
-
-
- Open
-
- relates to
-
CALCITE-6450 Postgres CONCAT_WS function throws exception when parameter type is (<CHAR(1)>, <INTEGER ARRAY>)
-
- Resolved
-
-
-
- Closed
-
-
-
- Closed
-
- links to