Table of ContentsPreviousNext

Ispirer             Ispirer


Ispirer SQLWays Database Migration Software

Concatenating Strings

This subsection describes string concatenation functions used in various databases and their conversion by SQLWays.

TABLE 52. Concatenating Strings
Database
Syntax
Description
Oracle
exp1 || exp2 [|| expN]... 
Returns the concatenation of two or more expressions of CHAR, VARCHAR2, CLOB data type.
Return type:
� If all expressions (for example exp1 and exp2) are of data type CHAR, the result has data type CHAR and is limited to 2000 characters.
� If either expression is of data type VARCHAR2, the result has data type VARCHAR2 and is limited to 4000 characters.
� If either expression is CLOB, the result is CLOB.
 
CONCAT (exp1, exp2) 
Returns exp1 concatenated with exp2. Both exp1 and exp2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
 
Return type:
� The string returned is in the same character set as exp1. Its data type depends on the datatypes of the arguments.
� In concatenations of two different datatypes, CONCAT function returns the data type that results in a lossless conversion.
� Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national data type, then the returned value is a national data type.
 
CONCAT function is equivalent to the concatenation operator (||).
MySQL
CONCAT(exp1, exp2 
[,expN]...) 
Returns the string that results from concatenating the arguments (expressions).
 
Returns NULL if any argument (expression) is NULL. A numeric argument is converted to its equivalent string form.
Microsoft SQL Server
exp1 + exp2 [+ expN]... 
Returns the concatenation of two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).
expression (exp1, exp2, ..., expN) - is expression of any of the data types in the character and binary data type category, except the image, ntext, or text data types. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.
An explicit conversion to character data must be used when concatenating binary strings and any characters between the binary strings.
Return type: the data type of the argument with the highest precedence. The precedence order data types is following:
sql_variant (highest)
� datetime
� smalldatetime
� float
� real
� decimal
� money
� smallmoney
� bigint
� int
� smallint
� tinyint
� bit
� ntext
� text
� image
� timestamp
� uniqueidentifier
� nvarchar
� nchar
� varchar
� char
� varbinary
� binary (lowest)
IBM DB2
CONCAT (exp1, exp2) 
exp1 || exp2 [|| expN]... 
exp1 CONCAT exp2 
[CONCAT expN]... 
Returns the concatenation of two or more string arguments. Arguments must be compatible types.
 
The result of the function and operator (||) is a string. Its length is the sum of the lengths of the two arguments. If either argument is null, the result is the null value.
 
Note: a binary string cannot be concatenated with a character string, including character strings defined as FOR BIT DATA.


Table of ContentsPreviousNext
Ispirer             Ispirer
Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.