MySQL SUBSTRING() Function - With Examples

Extract specific portions of a string using the MySQL SUBSTRING() function.

String and text data are so common and integral in today's data landscape that programming languages provide a full suite of functions, features, and constructs out of the box to help process this type of data.

Many times, there are only certain parts of a string or text value you need to parse, inspect, or work with.

If you need to get a specific portion of a text value with MySQL, you can use the SUBSTRING() function.

---

---

MySQL SUBSTRING() Syntax

There are 2 acceptable forms of syntax for the MySQL SUBSTRING() function:

SUBSTRING(target_string FROM start_position FOR length)

SUBSTRING(target_string, start_position, length)

The 3 parameter mean:

• target_string - required. This is the target string to extract the substring from.

• start_position - required. This position is where the substring extraction starts from. This whole number can be positive or negative. If negative, substring extraction starts from the end of target_string that many characters.

• length - optional. If a length parameter value is included in the SUBSTRING() function call, substring extraction begins at start_position and extracts n number of characters, where n is length value. If the length parameter value is omitted, substring extraction begins at the start_position and extracts characters until the end of target_string.

The best way to learn is by doing so we will run some queries with SUBSTRING().

---

Support OpenLampTech, my blog, and my work with a coffee if you would like.☕

---

SUBSTRING() Queries

For the examples in this article, I am using the ‘actor’ table from the well-known Sakila practice database.

To gain a sense of some of the present data, here are 5 rows from the ‘first_name’ column:

SUBSTRING() with all parameters

In this example query, the ‘first_name' column is the target_string. The 2nd parameter, 2, indicates extraction is to start from the 2nd character (counting starts at 1). The 3rd parameter of 3, indicates 3 characters will be extracted, beginning from the start_position:

SELECT first_name, 
SUBSTRING(first_name, 2, 3) AS start_2_take_3
FROM actor
LIMIT 5;

---

---

SUBSTRING() - no length parameter

Excluding the optional length parameter in this next query results in character extraction to the end of the target_string, from the 2nd character:

SELECT first_name, 
SUBSTRING(first_name, 2) AS start_2_take_the_rest
FROM actor
LIMIT 5;

Make note of the ‘first_name’ column value of ‘ED’ for the 3rd row. This piece of text has only 2 characters. Since the length parameter was not included in the SUBSTRING() function call, extraction continues from start_postition to the end of the target_string. In this case, only 1 remaining character is returned.

---

Do you need to learn MySQL? Just starting out and confused about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don't know where to start. Learn more about my premium MySQL blog posts and content as I develop and release them, along with an actionable MySQL tip each week.

---

SUBSTRING() with a negative start_position parameter

Using a negative start_position parameter begins extraction that many characters from the end of the target_string:

SELECT first_name, 
SUBSTRING(first_name, -2, 2) AS start_neg2_take_2
FROM actor
LIMIT 5;

SUBSTRING() - negative start_position parameter, no length parameter

In this example, I am using a negative start_position and no length value parameter. Therefore, the extraction begins at start_position number of characters from the end of the target_string, and continues to the end of target_string.

SELECT first_name, 
SUBSTRING(first_name, -4) AS start_neg4_take_rest
FROM actor
LIMIT 5;

We can see with the ‘first_name’ column value of ‘ED’ in the 3rd row, there are only 2 characters that make up the entire value so SUBSTRING() returns no substring for that particular row:

---

Related: Have some fun with SUBSTRING(). Read the post, RECURSIVE CTE Fun with SUBSTRING() in MySQL.

---

Play around with the SUBSTRING() function using different combinations of the parameters and you will be using it effectively in no time.

---

Similar Content

You may enjoy any of the below previously published articles and blog posts:

• MySQL Beginners Series — The SELECT statement

• MySQL IFNULL() function usage in SELECT queries

• Transform NULL values with the MySQL COALESCE function

PHP array_diff() function - A handy use case learned

MySQL SHOW TABLES - 2 variations

---

Visit my blog Digital Owl’s Prose, where I write regularly on CodeIgniter, PHP, and SQL. I’m also active on Medium, LinkedIn, Twitter, and Instagram.

---

If you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate content and are much appreciated.

---

How can I help you?

• Are you thinking of starting up a blog? I use WordPress for my blog, Digital Owl’s Prose. Let’s both save money on the plans offered. 💸

• Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. ✉️

• Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.

• I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.

• Grab a free pack of mobile Creator wallpapers with my referral link.

• Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.

---

Want to support this newsletter and my work? Drop some spare change in my Tip Jar.💰

Disclosure: Some of the services and products links in this email are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.