MySQL SUBSTRING() function

Last update on July 11 2024 06:05:43 (UTC/GMT +8 hours)

SUBSTRING() function

Introduction:

The SUBSTRING() function in MySQL is used to extract a specified number of characters from a particular position within a string. This function is invaluable for substring extraction and data manipulation tasks in SQL queries. It allows users to precisely select portions of strings based on their positions and lengths, enabling various data transformation and analysis tasks.

This function is useful in -

Substring extraction: We can extract a portion of a string by specifying its starting position and length.
Data manipulation: The SUBSTRING() function is often used in data manipulation tasks, such as extracting specific parts of a string for further processing or modifying the content of a string based on the desired substring.

Syntax:

SUBSTRING(str, pos, len)

SUBSTRING(str FROM pos FOR len)

Where:

str: The input string from which the substring will be extracted.

pos: The starting position within the string. If pos is positive, the extraction starts from the beginning of the string. If pos is negative, the extraction starts from the end of the string.

len: The length of the substring to be extracted.

Arguments:

Name	Description
str	A string expression.
pos	The starting position for substring extraction.
len	The length of the substring in characters.

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example : MySQL SUBSTRING() function

The following MySQL statement returns 3 numbers of characters from the 4th position of the string ‘w3resource’.

Code:


-- Using the SUBSTRING function to extract a portion of the string 'w3resource'
SELECT 
  -- Extract 3 characters starting from the 4th character in the string 'w3resource'
  SUBSTRING('w3resource', 4, 3);

Explanation:

Extract a substring from the given string 'w3resource'.

SUBSTRING(), which takes three arguments:

Input String: 'w3resource'.

Start Position: 4, starting from the 4th character.

Length: 3, extracting 3 characters.

The function extracts and returns 'eso' from 'w3resource', starting from the 4th character and spanning the next three characters.

Output:

mysql> SELECT SUBSTRING('w3resource',4,3);
+-----------------------------+
| SUBSTRING('w3resource',4,3) |
+-----------------------------+
| eso                         | 
+-----------------------------+
1 row in set (0.00 sec)

Example MySQL SUBSTRING() function using table

The following MySQL statement returns 5 numbers of characters from the 4th position of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.

Sample table: publisher

Code:


-- Select the columns 'pub_name' and the result of the SUBSTRING function from the 'publisher' table
SELECT 
  -- Retrieve the 'pub_name' column as it is
  pub_name,
  -- Extract 5 characters starting from the 4th character in the 'pub_name' column
  SUBSTRING(pub_name, 4, 5) 
FROM 
  -- Specify the 'publisher' table as the source of the data
  publisher 
WHERE 
  -- Apply a filter to select only the rows where the 'country' column equals 'USA'
  country = 'USA';

Explanation:

To extract and display specific portions of publisher names for publishers located in the USA.

Columns Selected:

pub_name: The original name of the publisher.

SUBSTRING(pub_name, 4, 5): Extracts a substring from the pub_name starting at the 4th character and includes the next 5 characters.

Source Table: publisher, which contains information about various publishers.

Condition: WHERE country='USA' filters the results to include only those publishers whose country is 'USA'.

The query returns the full name of each publisher (pub_name) and a 5-character substring starting from the 4th character of pub_name, but only for publishers based in the USA.

Output:

mysql> SELECT pub_name, SUBSTRING(pub_name,4,5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+-------------------------+
| pub_name                 | SUBSTRING(pub_name,4,5) |
+--------------------------+-------------------------+
| Jex Max Publication      |  Max                    | 
| Mountain Publication     | ntain                   | 
| Summer Night Publication | mer N                   | 
+--------------------------+-------------------------+
3 rows in set (0.00 sec)

The following MySQL statement returns the remaining characters from the 5th position of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.

Sample table: publisher

Code:


-- Select the columns 'pub_name' and the result of the SUBSTRING function from the 'publisher' table
SELECT 
  -- Retrieve the 'pub_name' column as it is
  pub_name,
  -- Extract the substring starting from the 5th character to the end of the 'pub_name' column
  SUBSTRING(pub_name, 5) 
FROM 
  -- Specify the 'publisher' table as the source of the data
  publisher 
WHERE 
  -- Apply a filter to select only the rows where the 'country' column equals 'USA'
  country = 'USA';

Explanation:

This query aims to extract and display publisher names and a specific portion of each name for publishers located in the USA.

Columns Selected:

pub_name: The full name of the publisher as stored in the table.

SUBSTRING(pub_name, 5): Extracts a substring from the pub_name column starting from the 5th character to the end of the string.

Source Table: publisher, which stores data about different publishers.

Condition: WHERE country='USA' ensures that only publishers based in the USA are included in the results.

The query returns the original name of each publisher (pub_name) and a substring that includes characters from the 5th position to the end of the publisher's name, but only for those publishers who are located in the USA.

Output:

mysql> SELECT pub_name, SUBSTRING(pub_name,5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+-----------------------+
| pub_name                 | SUBSTRING(pub_name,5) |
+--------------------------+-----------------------+
| Jex Max Publication      | Max Publication       | 
| Mountain Publication     | tain Publication      | 
| Summer Night Publication | er Night Publication  | 
+--------------------------+-----------------------+
3 rows in set (0.00 sec)

Example MySQL SUBSTRING() function with FROM keyword

The following MySQL statement returns the remaining characters from the 5th position (Notice that FROM keyword is used) of the column pub_name for those publishers who belong to the country ‘USA’ from the table publisher.

Sample table: publisher

Code:


-- Select the columns 'pub_name' and the result of the SUBSTRING function from the 'publisher' table
SELECT 
  -- Retrieve the full name of the publisher from the 'pub_name' column
  pub_name,
  -- Extract a substring from the 'pub_name' column starting at the 5th character
  SUBSTRING(pub_name FROM 5) 
FROM 
  -- Specify the 'publisher' table as the source of the data
  publisher 
WHERE 
  -- Apply a filter to select only the rows where the 'country' column equals 'USA'
  country = 'USA';

Explanation:

This query is designed to display the full names of publishers and a substring of their names for those based in the USA.

Columns Selected:

pub_name: Displays the full name of each publisher from the pub_name column.

SUBSTRING(pub_name FROM 5): Extracts a substring from each pub_name, starting from the 5th character to the end of the string. The FROM keyword specifies where the substring extraction begins.

Source Table: publisher, which contains records of various publishers, including their names and other attributes.

Condition: WHERE country='USA' filters the rows to include only publishers whose country field is set to 'USA'.

The query outputs the full publisher names and their corresponding substrings (starting from the 5th character onward) for those publishers who are based in the USA. This helps in analyzing or displaying parts of the publisher names in a more focused way.

Output:

mysql> SELECT pub_name, SUBSTRING(pub_name FROM 5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+----------------------------+
| pub_name                 | SUBSTRING(pub_name FROM 5) |
+--------------------------+----------------------------+
| Jex Max Publication      | Max Publication            | 
| Mountain Publication     | tain Publication           | 
| Summer Night Publication | er Night Publication       | 
+--------------------------+----------------------------+
3 rows in set (0.00 sec)

Example of MySQL SUBSTRING() to extracts from a negative position

The following MySQL statement returns the remaining characters from the 4th position from the end of the column pub_name instead of the beginning for those publishers who belong to the country ‘USA’ from the table publisher. In this case the value of position of the beginning of the retrieval is negative. So the beginning of the retrieval is 4 characters from the end of the string rather than the beginning.

Sample table: publisher

Code:


-- Select the columns 'pub_name' and the result of the SUBSTRING function from the 'publisher' table
SELECT 
  -- Retrieve the full name of the publisher from the 'pub_name' column
  pub_name,
  -- Extract a substring from the 'pub_name' column starting 4 characters from the end of the string
  SUBSTRING(pub_name, -4)
FROM 
  -- Specify the 'publisher' table as the source of the data
  publisher 
WHERE 
  -- Apply a filter to select only the rows where the 'country' column equals 'USA'
  country = 'USA';

Explanation:

This query is designed to retrieve and display the full names of publishers along with a specific substring of their names for those based in the USA.

Columns Selected:

pub_name: Displays the full name of each publisher from the pub_name column.

SUBSTRING(pub_name, -4): Extracts a substring from each pub_name, starting 4 characters from the end of the string. The negative position value -4 indicates that the extraction starts from the fourth character from the end and continues to the end of the string.

Source Table: publisher, which contains records of various publishers, including their names and other attributes.

Condition: WHERE country='USA' filters the rows to include only publishers whose country field is set to 'USA'.

The query outputs the full publisher names and the last 4 characters of their names for those publishers who are based in the USA. This can be useful for focusing on or analyzing the end portion of publisher names, which might include a suffix or significant ending detail.

Output:

mysql> SELECT pub_name, SUBSTRING(pub_name ,-4) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+----------------------------+
| pub_name                 | SUBSTRING(pub_name , -4)   |
+--------------------------+----------------------------+
| Jex Max Publication      | tion                       | 
| Mountain Publication     | tion                       | 
| Summer Night Publication | tion                       | 
+--------------------------+----------------------------+
3 rows in set (0.00 sec)

Example of MySQL SUBSTRING() function extracts from the end

The following MySQL statement returns the 5 number of characters from the 15th position from the end of the column pub_name instead of the beginning for those publishers who belong to the country ‘USA’ from the table publisher. In this case the value of position of the beginning of the retrieval is negative. So the beginning of the retrieval is 15 characters from the end of the string rather than the beginning.

Sample table: publisher

Code:


-- Select the columns 'pub_name' and a substring from 'pub_name' from the 'publisher' table
SELECT 
  -- Retrieve the full name of the publisher from the 'pub_name' column
  pub_name,
  -- Extract a substring from 'pub_name' starting 15 characters from the end, with a length of 5 characters
  SUBSTRING(pub_name, -15, 5)
FROM 
  -- Specify the 'publisher' table as the source of the data
  publisher 
WHERE 
  -- Apply a filter to select only the rows where the 'country' column equals 'USA'
  country = 'USA';

Explanation:

The query extracts a specific substring from the names of publishers who are located in the USA.

Columns Selected:

pub_name: This column displays the full name of each publisher.

SUBSTRING(pub_name, -15, 5): This function extracts a 5-character substring from each pub_name, starting 15 characters from the end of the string. The use of a negative starting position (-15) indicates that the extraction begins 15 characters from the end of the string and continues for the next 5 characters.

Source Table: publisher, which contains information about various publishers, including their names and other attributes.

Condition: WHERE country='USA' filters the results to include only publishers whose country field is set to 'USA'.

The query returns a list of publishers from the USA, showing their full names and a 5-character substring that starts 15 characters from the end of their names. This can be particularly useful for analyzing or highlighting specific portions of publisher names that may have important suffixes or distinguishing segments.

Output:

mysql> SELECT pub_name, SUBSTRING(pub_name ,-15,5) 
    -> FROM publisher 
    -> WHERE country='USA';
+--------------------------+----------------------------+
| pub_name                 | SUBSTRING(pub_name ,-15,5) |
+--------------------------+----------------------------+
| Jex Max Publication      | Max P                      | 
| Mountain Publication     | ain P                      | 
| Summer Night Publication | ght P                      | 
+--------------------------+----------------------------+
3 rows in set (0.00 sec)

Example of MySQL SUBSTRING() using FROM and FOR keywords

The following MySQL statement returns the 5 (Notice that FOR keyword is used) numbers of characters from the 15th (Notice that FROM keyword is used) position from the end of the column pub_name instead of the beginning for those publishers who belong to the country ‘USA’ from the table publisher. In this case, the value of the position of the beginning of the retrieval is negative. So the beginning of the retrieval is 15 characters from the end of the string rather than the beginning.

Sample table: publisher

Code:


-- Select the columns 'pub_name' and a substring from 'pub_name' using negative indexing from the 'publisher' table
SELECT 
  -- Retrieve the full name of the publisher from the 'pub_name' column
  pub_name,
  -- Extract a substring from 'pub_name' starting 15 characters from the end, with a length of 5 characters
  SUBSTRING(pub_name FROM -15 FOR 5)
FROM 
  -- Specify the 'publisher' table as the source of the data
  publisher 
WHERE 
  -- Apply a filter to select only the rows where the 'country' column equals 'USA'
  country = 'USA';

Explanation:

This query extracts a specific substring from the names of publishers who are located in the USA using negative indexing.

Columns Selected:

pub_name: Displays the full name of each publisher.

SUBSTRING(pub_name FROM -15 FOR 5): This function extracts a substring starting from the 15th character from the end of pub_name and includes the next 5 characters. Negative indexing in SUBSTRING starts counting from the end of the string (-1 being the last character), so -15 means starting 15 characters from the end.

Source Table: publisher, which contains information about various publishers, including their names and other attributes.

Condition: WHERE country='USA' filters the results to include only publishers whose country field is set to 'USA'.

Result: The query returns a list of publishers from the USA, showing their full names and a 5-character substring that starts 15 characters from the end of their names. This substring extraction can be useful for analyzing specific portions of publisher names that may have important suffixes or distinguishing segments.

Output:

mysql> SELECT pub_name, 
    -> SUBSTRING(pub_name FROM -15 FOR 5) 
    -> FROM publisher 
    -> WHERE country='USA'; 
+--------------------------+------------------------------------+
| pub_name                 | SUBSTRING(pub_name FROM -15 FOR 5) |
+--------------------------+------------------------------------+
| Jex Max Publication      | Max P                              | 
| Mountain Publication     | ain P                              | 
| Summer Night Publication | ght P                              | 
+--------------------------+------------------------------------+
3 rows in set (0.00 sec)

Frequently Asked Questions (FAQ) - MySQL SUBSTRING() Function

1. What is the MySQL SUBSTRING() function?

The MySQL SUBSTRING() function is used to extract a specified number of characters from a particular position within a string. It allows for precise selection of string portions based on their positions and lengths

2. What are the main uses of the MySQL SUBSTRING() function?

Substring Extraction: To retrieve a specific part of a string by indicating the starting position and the number of characters to extract.

Data Manipulation: Often used to extract or modify specific parts of a string for further processing or transformation.

3. How does the MySQL SUBSTRING() function improve data manipulation?

The SUBSTRING() function simplifies the process of working with string data by allowing the extraction of specific segments based on their positions within the string. This is particularly useful for tasks like extracting domain names from email addresses or splitting data stored in delimited formats.

4. Can MySQL SUBSTRING() handle negative positions?

Yes, the SUBSTRING() function can handle negative positions. When a negative position is specified, the function counts from the end of the string towards the beginning.

5. What happens if the specified length exceeds the actual string length?

If the length specified in the SUBSTRING() function exceeds the number of characters available from the starting position to the end of the string, the function will return all characters from the starting position to the end of the string.

6. Can MySQL SUBSTRING() be used with columns in database tables?

Yes, the SUBSTRING() function can be used to extract parts of strings stored in table columns. This is useful for manipulating and transforming data directly within SQL queries.

7. What are common scenarios where MySQL SUBSTRING() is applied?

Data Cleaning: Removing or extracting specific parts of a string to clean up data.

Pattern Matching: Extracting substrings that match certain patterns, such as area codes from phone numbers.

String Splitting: Breaking down delimited strings into individual components.

8. How does MySQL SUBSTRING() compare to other string functions like LEFT() and RIGHT()?

SUBSTRING(): Extracts characters from any position within a string.

LEFT(): Extracts a specified number of characters from the beginning of a string.

RIGHT(): Extracts a specified number of characters from the end of a string.

9. Can MySQL SUBSTRING() be used in conjunction with other SQL functions?

Yes, SUBSTRING() is often used alongside other SQL functions like CONCAT(), LENGTH(), and REPLACE() to perform more complex string manipulations and transformations.

10. Is there a way to extract multiple substrings using MySQL SUBSTRING()?

To extract multiple substrings, you can nest multiple SUBSTRING() calls or combine them with other string functions within a single query. However, this can make queries more complex and potentially harder to maintain.

Video Presentation:

All String Functions (Slides presentation)

Previous: SUBSTRING_INDEX
Next: TRIM