Concatenating Strings
String concatenation functions used in various databases and their conversion by SQLWays.
SQL 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.
|
- MySQL: CONCAT()
- Oracle: CONCAT(), ||
- SQL Server: +
The syntax for CONCAT() is as follows:
CONCAT(str1, str2, str3, ...):
Concatenate str1, str2, str3, and any other strings together. Please
note the Oracle CONCAT() function only allows two arguments -- only two
strings can be put together at a time using this function. However, it
is possible to concatenate more than two strings at a time in Oracle
using '||'.
Let's look at some examples. Assume we have the following table:
Table Geography
| region_name |
store_name |
| East |
Boston |
| East |
New York |
| West |
Los Angeles |
| West |
San Diego |
Example 1:
MySQL/Oracle:
SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = 'Boston';
Result:
'EastBoston'
Example 2:
Oracle:
SELECT region_name || ' ' || store_name FROM Geography
WHERE store_name = 'Boston';
Result:
'East Boston'
Example 3:
SQL Server:
SELECT region_name + ' ' + store_name FROM Geography
WHERE store_name = 'Boston';
Result:
'East Boston'
he Substring function in SQL is used to grab a portion of the stored data. This
function is called differently for the different databases:
- MySQL: SUBSTR(), SUBSTRING()
- Oracle: SUBSTR()
- SQL Server: SUBSTRING()
The most frequent uses are as follows (we will use SUBSTR() here):
SUBSTR(str,pos): Select all
characters from <str> starting with position <pos>. Note
that this syntax is not supported in SQL Server.
SUBSTR(str,pos,len): Starting with the <pos>th
character in string <str> and select the next <len> characters.
Assume we have the following table:
Table Geography
| region_name |
store_name |
| East |
Boston |
| East |
New York |
| West |
Los Angeles |
| West |
San Diego |
Example 1:
SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';
Result:
's Angeles'
Example 2:
SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';
Result:
'an D'
The TRIM function in SQL is used to remove specified prefix or suffix from a string.
The most common pattern being removed is white spaces. This function is called
differently in different databases:
- MySQL: TRIM(), RTRIM(), LTRIM()
- Oracle: RTRIM(), LTRIM()
- SQL Server: RTRIM(), LTRIM()
The syntax for these trim functions are:
TRIM([[LOCATION] [remstr] FROM ] str): [LOCATION] can be either LEADING, TRAILING, or BOTH. This function gets rid of the [remstr] pattern
from either the beginning of the string or the end of the string, or both. If no [remstr] is
specified, white spaces are removed.
LTRIM(str): Removes all white spaces from the beginning of the string.
RTRIM(str): Removes all white spaces at the end of the string.
Example 1:
SELECT TRIM(' Sample ');
Result:
'Sample'
Example 2:
SELECT LTRIM(' Sample ');
Result:
'Sample '
Example 3:
SELECT RTRIM(' Sample ');
Result:
' Sample'
miércoles, 09 de abril de 2008
