Example
Reverse a string:
SELECT REVERSE("SQL Tutorial");
Definition and Usage
The REVERSE() function reverses a string and returns the result. Continue reading MySQL REVERSE Function
Category: SQL
MySQL REPLACE Function
Example
Replace “SQL” with “HTML”:
SELECT REPLACE("SQL Tutorial", "SQL", "HTML");
Definition and Usage
The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.
Note: This function performs a case-sensitive replacement. Continue reading MySQL REPLACE Function
MySQL REPEAT Function
Example
Repeat a string 3 times:
SELECT REPEAT("SQL Tutorial", 3);
Definition and Usage
The REPEAT() function repeats a string as many times as specified. Continue reading MySQL REPEAT Function
MySQL POSITION Function
Example
Search for “3” in string “Iampsp.com”, and return position:
SELECT POSITION(“3” IN “Iampsp.com”) AS MatchPosition;
Definition and Usage
The POSITION() 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. Continue reading MySQL POSITION Function
MySQL MID Function
Example
Extract a substring from a string (start at position 5, extract 3 characters):
SELECT MID("SQL Tutorial", 5, 3) AS ExtractString;
Definition and Usage
The MID() function extracts a substring from a string (starting at any position).
Note: The position of the first character in the string is 1.
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| S | Q | L | T | u | t | o | r | i | a | l |
Note: The position of the last character in the string is -1.
| -12 | -11 | -10 | -9 | -8 | -7 | -6 | -5 | -4 | -3 | -2 | -1 |
| S | Q | L | T | u | t | o | r | i | a | l |
Note: The MID() and SUBSTR() functions equals the SUBSTRING() function. Continue reading MySQL MID Function
MySQL LTRIM Function
Example
Remove leading spaces from a string:
SELECT LTRIM(" SQL Tutorial") AS LeftTrimmedString;
Definition and Usage
The LTRIM() function removes leading spaces from a string.
Syntax
LTRIM(string)
Parameter Values
| Parameter | Description |
|---|---|
| string | Required. The string to remove leading spaces from |
Technical Details
| Works in: | From MySQL 4.0 |
|---|
MySQL LPAD Function
Example
Left-pad the string with “ABC”, to a total length of 20:
SELECT LPAD("SQL Tutorial", 20, "ABC");
Definition and Usage
The LPAD() function left-pads a string with another string, to a certain length.
Note: Also look at the RPAD() function.
Syntax
LPAD(string, length, lpad_string)
Parameter Values
| 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 |
Technical Details
| Works in: | From MySQL 4.0 |
|---|
More Examples
Example
Left-pad the text in “CustomerName” with “ABC”, to a total length of 30:
SELECT LPAD(CustomerName, 30, "ABC") AS LeftPadCustomerName
FROM Customers;
MySQL LOWER Function
Example
Convert the text to lower-case:
SELECT LOWER(“SQL Tutorial is FUN!”);
Definition and Usage
The LOWER() function converts a string to lower-case.
Note: The LCASE() function is equal to the LOWER() function.
Syntax
LOWER(text)
Parameter Values
| Parameter | Description |
|---|---|
| text | Required. The string to convert |
Technical Details
| Works in: | From MySQL 4.0 |
|---|
More Examples
Example
Convert the text in “CustomerName” to lower-case:
SELECT LOWER(CustomerName) AS LowercaseCustomerName
FROM Customers;
MySQL LOCATE Function
Example
Search for “3” in string “Iampsp.com”, and return position:
SELECT LOCATE("3", "Iampsp.com") AS MatchPosition;
Definition and Usage
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.
Syntax
LOCATE(substring, string, start)
Parameter Values
| 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 |
Technical Details
| Works in: | From MySQL 4.0 |
|---|
More Examples
Example
Search for “com” in string “Iampsp.com” (start at position 3), and return position :
SELECT LOCATE("com", "Iampsp.com", 3) AS MatchPosition;
Example
Search for “a” in CustomerName column, and return position:
SELECT LOCATE("a", CustomerName)
FROM Customers;
MySQL LENGTH Function
Example
Return the length of the string, in bytes:
SELECT LENGTH("SQL Tutorial") AS LengthOfString;
Definition and Usage
The LENGTH() function returns the length of a string (in bytes).
Syntax
LENGTH(string)
Parameter Values
| Parameter | Description |
|---|---|
| string | Required. The string to count the length for |
Technical Details
| Works in: | From MySQL 4.0 |
|---|
More Examples
Example
Return the length of the text in the “CustomerName” column, in bytes:
SELECT LENGTH(CustomerName) AS LengthOfName
FROM Customers;