Remove leading spaces from a string:
SELECT LTRIM(" SQL Tutorial") AS LeftTrimmedString;
The LTRIM() function removes leading spaces from a string.
LTRIM(string)
| Parameter | Description |
|---|---|
| string | Required. The string to remove leading spaces from |
| Works in: | From MySQL 4.0 |
|---|
Left-pad the string with “ABC”, to a total length of 20:
SELECT LPAD("SQL Tutorial", 20, "ABC");
The LPAD() function left-pads a string with another string, to a certain length.
Note: Also look at the RPAD() function.
LPAD(string, length, lpad_string)
| Parameter | Description |
|---|---|
| string | Required. The original string. If the length of the original string is larger than the length parameter, this function removes the overfloating characters from string |
| length | Required. The length of the string after it has been left-padded |
| lpad_string | Required. The string to left-pad to string |
| Works in: | From MySQL 4.0 |
|---|
Left-pad the text in “CustomerName” with “ABC”, to a total length of 30:
SELECT LPAD(CustomerName, 30, "ABC") AS LeftPadCustomerName
FROM Customers;
Convert the text to lower-case:
SELECT LOWER(“SQL Tutorial is FUN!”);
The LOWER() function converts a string to lower-case.
Note: The LCASE() function is equal to the LOWER() function.
LOWER(text)
| Parameter | Description |
|---|---|
| text | Required. The string to convert |
| Works in: | From MySQL 4.0 |
|---|
Convert the text in “CustomerName” to lower-case:
SELECT LOWER(CustomerName) AS LowercaseCustomerName
FROM Customers;
Search for “3” in string “Iampsp.com”, and return position:
SELECT LOCATE("3", "Iampsp.com") AS MatchPosition;
The LOCATE() function returns the position of the first occurrence of a substring in a string.
If the substring is not found within the original string, this function returns 0.
This function performs a case-insensitive search.
Note: This function is equal to the POSITION() function.
LOCATE(substring, string, start)
| Parameter | Description |
|---|---|
| substring | Required. The substring to search for in string |
| string | Required. The string that will be searched |
| start | Optional. The starting position for the search. Position 1 is default |
| Works in: | From MySQL 4.0 |
|---|
Search for “com” in string “Iampsp.com” (start at position 3), and return position :
SELECT LOCATE("com", "Iampsp.com", 3) AS MatchPosition;
Search for “a” in CustomerName column, and return position:
SELECT LOCATE("a", CustomerName)
FROM Customers;
Return the length of the string, in bytes:
SELECT LENGTH("SQL Tutorial") AS LengthOfString;
The LENGTH() function returns the length of a string (in bytes).
LENGTH(string)
| Parameter | Description |
|---|---|
| string | Required. The string to count the length for |
| Works in: | From MySQL 4.0 |
|---|
Return the length of the text in the “CustomerName” column, in bytes:
SELECT LENGTH(CustomerName) AS LengthOfName
FROM Customers;
Extract 3 characters from a string (starting from left):
SELECT LEFT("SQL Tutorial", 3) AS ExtractString;
The LEFT() function extracts a number of characters from a string (starting from left).
Tip: Also look at the RIGHT() function. Continue reading MySQL LEFT Function
Convert the text to lower-case:
SELECT LCASE("SQL Tutorial is FUN!");
The LCASE() function converts a string to lower-case.
Note: The LOWER() function is a synonym for the LCASE() function.
LCASE(text)
| Parameter | Description |
|---|---|
| text | Required. The string to convert |
| Works in: | From MySQL 4.0 |
|---|
Convert the text in “CustomerName” to lower-case:
SELECT LCASE(CustomerName) AS LowercaseCustomerName
FROM Customers;
Search for “3” in string “Iampsp.com”, and return position:
SELECT INSTR("Iampsp.com", "3") AS MatchPosition;
The INSTR() function returns the position of the first occurrence of a string in another string.
This function performs a case-insensitive search. Continue reading MySQL INSTR Function
Insert the string “Example” into the string “Iampsp.com”. Replace the first nine characters:
SELECT INSERT("Iampsp.com", 1, 9, "Example");
The INSERT() function inserts a string within a string at the specified position and for a certain number of characters. Continue reading MySQL INSERT Function
Format the number as “#,###,###.##” (and round with two decimal places):
SELECT FORMAT(250500.5634, 2);
The FORMAT() function formats a number to a format like “#,###,###.##”, rounded to a specified number of decimal places, then it returns the result as a string. Continue reading MySQL FORMAT Function