Example:
USE world;
SELECT CONCAT(name, ', ', continent)
FROM country;
Results:
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:
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:
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:
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(), 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:
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/learning_mysql/string_functions.