The concat function (expression) may present an inconsistent behavior. It seems that it is possible to have a non-string argument only if it is listed as the very first one. Otherwise, an error message is displayed.
Example 1: concat expression with only one non-string argument
pi concat(a)->x (R)
or
SELECT concat(a) AS x FROM R;
results in
x
'1'
'3'
'4'
'5'
'6'
Example 2: concat expression with multiple non-string arguments
pi concat(a, a)->x (R)
or
SELECT concat(a, a) AS x FROM R;
results in
Error: at line 1: CONCAT() expects all arguments to be of the same type
Example 3: concatenate non-string with string arguments (non-string as the first listed)
pi concat(a, b, c)->x (R)
or
SELECT concat(a, b, c) AS x FROM R;
results in
x
'1ad'
'3cc'
'4df'
'5db'
'6ef'
Example 4: concatenate string with non-string argument
pi concat(b, a)->x (R)
or
SELECT concat(b, a) AS x FROM R;
results in
Error: at line 1: CONCAT() expects all arguments to be of the same type
Bottom line: If a non-string argument is listed as the first in the concat expression it is implicitly converted to string and then concatenated. In case a non-string argument shows up in another position, an error message is displayed.
How is this implemented on RDBMS?
MySQL (since version 5.1)
CONCAT(str1,str2,...): Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form. CONCAT() returns NULL if any argument is NULL.
PosgreSQL (since version 9.1)
concat ( val1 "any" [, val2 "any" [, ...] ] ) → text: Concatenates the text representations of all the arguments. NULL arguments are ignored.
SQL Server (since version 2012)
CONCAT ( string_value1, string_value2 [, string_valueN ] ): CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.
What is this issue about?
The
concatfunction (expression) may present an inconsistent behavior. It seems that it is possible to have a non-string argument only if it is listed as the very first one. Otherwise, an error message is displayed.NOTE: To test the examples refer to http://dbis-uibk.github.io/relax/calc/local/uibk/local/0
concatexpression with only one non-string argumentor
results in
concatexpression with multiple non-string argumentsor
results in
or
results in
or
results in
Bottom line: If a non-string argument is listed as the first in the concat expression it is implicitly converted to string and then concatenated. In case a non-string argument shows up in another position, an error message is displayed.
How is this implemented on RDBMS?
CONCAT(str1,str2,...): Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form. CONCAT() returns NULL if any argument is NULL.concat ( val1 "any" [, val2 "any" [, ...] ] ) → text: Concatenates the text representations of all the arguments. NULL arguments are ignored.CONCAT ( string_value1, string_value2 [, string_valueN ] ): CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.References: