CONCAT
- Combines a list of strings into a single string.
- Can include column values and literal values.
- In MySQL literal values can be enclosed with either single ( ‘ ) or double quotes ( “ ) .
Example:
USE world;
SELECT CONCAT(name, ', ', continent)
FROM country;
Results:
RIGHT, LEFT
- The RIGHT and LEFT functions have two parameters. The first is a string and the second is the number of characters to be returned.
- The RIGHT function starts counting from the right side of the string. • The LEFT function starts counting from the left side of the string.
Table 7. RIGHT, LEFT functions
| Function |
Type |
Example |
Result |
|
RIGHT(string, num. characters)
|
string
|
RIGHT(‘Salmon’, 3)
|
mon
|
|
LEFT(string, num. characters)
|
string
|
LEFT(‘Salmon’, 3)
|
Sal
|
Example:
USE bike;
SELECT category_name,
LEFT(category_name, 8) AS 'First 8 Characters',
RIGHT(category_name, 8) AS 'Last 8 Characters'
FROM category;
Results:
TRIM, LTRIM, RTRIM
- The TRIM function will remove leading and trailing spaces from a string.
- The LTRIM function will remove leading spaces from a string.
- The RTRIM function will remove trailing spaces from a string.
Table 8. TRIM functions
| Function |
Type |
Example |
Result |
|
TRIM(string)
|
string
|
TRIM(‘ Salmon ‘)
|
‘salmon’
|
|
LTRIM(string)
|
string
|
LEFT(‘Salmon ‘)
|
‘salmon ’
|
|
RTRIM(string)
|
string
|
RIGHT(‘ Salmon‘)
|
‘ salmon’
|
Example:
SELECT LTRIM(' Salmon ') AS "Left Trim",
RTRIM(' Salmon ') AS "Right Trim",
TRIM(' Salmon ') AS "Trim";
Results:
FORMAT
- FORMAT() accepts a decimal but returns a comma formatted string.
Table 9. FORMAT functions
| Function |
Type |
Example |
Result |
|
FORMAT(number, decimal)
|
string
|
FORMAT(1234.342, 2)
|
-356
|
Code Sample:
SELECT FORMAT(list_price,2)
FROM bike.product;
Results:
LOWER, UPPER
- LOWER() converts all characters to lower case.
- UPPER() converts all characters to upper case.
Table 9. LOWER, UPPER functions
| Function |
Type |
Example |
Result |
|
LOWER(string)
|
string
|
LOWER(‘Salmon ‘)
|
‘salmon’
|
|
UPPER(string)
|
string
|
UPPER(‘Salmon‘)
|
‘SALMON’
|
Example:
SELECT UPPER('Salmon'),
LOWER('Salmon');
Results:
LOCATE, LENGTH, SUBSTRING
LOCATE(), and LENGTH() accept a string but return an integer. • SUBSTRING() accepts a string and returns a string.
Table 9. LOCATE. LENGTH, SUBSTRING functions
| Function |
Type |
Example |
Result |
|
LOCATE(find,search[,start])
|
string
|
LOCATE(‘al’,’salmon’,1)
|
2
|
|
LENGTH(str)
|
string
|
LENGTH(‘salmon’)
|
6
|
|
SUBSTRING(str,start[,length])
|
string
|
SUBSTRING('salmon',3,999)
|
‘lmon’
|
Example:
SELECT LOCATE('al','salmon',1),
LENGTH('salmon'),
SUBSTRING('salmon',3,999);
Results:
