The SQL standard defines a concatenation operator ( || ), which joins two or more strings into one string value. The CONCAT(str1, str2...) function can have one or more arguments. It returns a string that is the result of concatenating the arguments.
Syntax:
CONCAT(str1, str2, [,...n]) For example:
SELECT CONCAT('Dr', 'izzl', 'ed');
Returns: ‘Drizzled’
SELECT CONCAT('micro', NULL, 'nel');
Returns: NULL
SELECT CONCAT(14.8);
Returns: ‘14.8’
CONCAT WS (With Separator) [1] allows you to specify that the first argument is treated as a separator for the rest of the arguments. This argument is added between the strings to be concatenated.
Syntax:
CONCAT_WS(separator, str1, str2,....)
For example:
SELECT CONCAT_WS(',', ' Occupation', 'First name', 'Last Name');
Returns: ‘Occupation, First name, Last Name’
SELECT CONCAT_WS(',', 'First name', NULL, 'Last Name');
Returns: ‘First name, Last Name’
The TRIM function remove specified prefixes or suffixes from a string (typically leading and trailing spaces), and returns the resulting string. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed.
Syntax:
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
[remstr] is optional (if it’s not specified, spaces are removed).
For example:
SELECT TRIM(BOTH 'hello,' FROM 'hello,Drizzlehello,');
Returns: ‘Drizzle’
This version of the TRIM function removes leading spaces from the beginning of a string.
For example:
SELECT LTRIM(' Drizzle');
Returns: ‘Drizzle’