The string functions in MySQL are essential tools for manipulating text data. They allow operations such as concatenating strings, converting data types, searching and replacing substrings, among others. This article will introduce some of the most used string functions in MySQL and examples of how to use them.
CONCAT() function
The CONCAT() function is used to concatenate two or more strings. For example, if we have two columns 'first name' and 'last name' and we want to merge them into a single column 'full name', we can use the CONCAT() function.
SELECT CONCAT(firstname, ' ', lastname) AS 'Full Name' FROM users;
This query will return a 'Full Name' column with each user's first and last name concatenated with a space between them.
LENGTH() function
The LENGTH() function returns the length of a string in bytes. This can be useful for validating the length of input data or for finding the longest or shortest string in a dataset.
SELECT LENGTH(name) AS 'Length of Name' FROM users;
This query will return a 'Name Length' column with the length of each user's name in bytes.
Continue in our app.
You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.
Or continue reading below...
Download the app
REPLACE() function
The REPLACE() function is used to replace all occurrences of a substring with another substring in a string. For example, if we want to replace all occurrences of the letter 'a' with the letter 'e' in a column 'name', we can use the REPLACE() function.
SELECT REPLACE(name, 'a', 'e') AS 'Modified Name' FROM users;
This query will return a 'Modified Name' column with all occurrences of the letter 'a' replaced by the letter 'e' in each user's name.
SUBSTRING() function
The SUBSTRING() function is used to extract a substring from a string. It accepts three parameters: the original string, the starting position and the length of the substring to be extracted.
SELECT SUBSTRING(name, 1, 3) AS 'First Three Characters' FROM users;
This query will return a 'First Three Characters' column with the first three characters of each user's name.
FUNCTION LOWER() and UPPER()
The LOWER() and UPPER() functions are used to convert a string to lowercase and uppercase respectively.
SELECT LOWER(name) AS 'Name in Lowercase', UPPER(name) AS 'Name in Uppercase' FROM users;
This query will return two columns, 'Name in Lowercase' and 'Name in Uppercase', with each user's name converted to lowercase and uppercase respectively.
TRIM() function
The TRIM() function is used to remove whitespace from the beginning and end of a string.
SELECT TRIM(name) AS 'Name Without Spaces' FROM users;
This query will return a 'Name Without Spaces' column with each user's name without leading or trailing whitespace.
These are just a few of the many string functions available in MySQL. Each of them can be extremely useful in different scenarios, depending on your specific string manipulation needs. Therefore, it is important to know and understand how to use these functions to manipulate and parse text data in MySQL effectively.
