MySQL Part 4: String Functions
Introduction
In this article, we will walk through some of the more popular string functions available in MySQL. For an extensive list of string functions, check out the link below.
In Part 3 of this series, we created the tables and the data presented in the article below. For more information about this article, click the link below.
In Part 4, we will use the bike shop data to add string functions to existing columns. If you started a new MySQL session, please type the following into the terminal and press <Enter>.
mysql-ctl cli
Please ensure that the bike_shop.sql file is selected and available. If not, double-click it on the left-hand side. This is where we will type most of our code. If there is information saved in there, go ahead and clear out that information.
Add String Functions to Columns
We created a bikes table that included a previous article's bike_id, model, description, and price. To check out the table structure and contents of the bikes table, type the following code into the terminal and press <Enter> after each line of code.
CONCAT Function
If we want to combine the bike_id column with the model into one column, separated by a hyphen, "-," one option is to use the CONCAT function. See code and result below.
SELECT CONCAT(bike_id, '-', model) AS id_model FROM bikes;
After successfully copying and pasting the code into the goormIDE, press Ctrl+S to save the file, then, as we have done previously, go down to the Terminal section and type the following code, and press <Enter> to run the source file.
SOURCE bike_shop.sql;
The results of the query are listed below.
SUBSTRING Function
The SUBSTRING function pulls out some characters based on their positions within the text. For example, we would use the following code to pull out "Supersix" out of the model column.
SELECT model, SUBSTRING(model, 1, 8) AS brand FROM bikes;
Be sure to run the source file in the terminal section.
SOURCE bike_shop.sql;
The results of the query are listed below.
REPLACE Function
The REPLACE function finds a string and replaces it with another string that you define. In the code below, we replace "Evo" in the model column with "Evolution".
SELECT model, REPLACE(model, 'Evo', 'Evolution') AS model_formal FROM bikes;
Be sure to run the source file in the terminal section.
SOURCE bike_shop.sql;
The results of the query are listed below.
CHAR_LENGTH Function
The CHAR_LENGTH function counts the number of characters in a given string. This function is helpful when you want to find the last 3 characters of various length strings.
SELECT model, CHAR_LENGTH(model) AS Char_Length FROM bikes;
Be sure to run the source file in the terminal section.
SOURCE bike_shop.sql;
The results of the query are listed below.
UPPER and LOWER Functions
The UPPER function will make all characters uppercase within the entire string, while the LOWER will turn all characters into lowercase. See some simple examples of both below.
SELECT model, UPPER(model) AS Upper FROM bikes;
Be sure to run the source file in the terminal section.
SOURCE bike_shop.sql;
The results of the query are listed below.
SELECT model, LOWER(model) AS Lower FROM bikes;
Be sure to run the source file in the terminal section.
SOURCE bike_shop.sql;
The results of the query are listed below.
Many other string functions are included in the MySQL documentation, but some of the most commonly used string functions.
For a complete list of the entire set of MySQL-related articles, please see the links below.
MySQL Part 1: goormIDE
MySQL Part 2: Create Database
MySQL Part 3: Create Tables
MySQL Part 4: String Functions
MySQL Part 5: Refining Select Statements
MySQL Part 6: Aggregations
MySQL Part 7: Dates
MySQL Part 8: Logical Operations
MySQL Part 9: Joins
