How to use the SUBSTRING function to extract part of a string in MySQL

How to use the SUBSTRING function in MySQL to extract a part of a string

In MySQL, the SUBSTRING function is used to extract a substring of a string. It can intercept a part of the string based on the specified starting position and length. Using the SUBSTRING function can easily get the data we need, such as extracting the date part from a complete date and time string, or extracting a summary of a specified length from a long text.

The syntax of the SUBSTRING function is as follows:

SUBSTRING(str, start [, length])

Among them, the str parameter is the string to extract the substring; the start parameter is The starting position is the index number starting from 1; the length parameter is the length of the substring to be extracted.

The following uses some specific examples to illustrate how to use the SUBSTRING function.

Example 1: Extract the date part from the complete date and time string

Suppose we have the following data table "orders":

##2020-02-01 09:30:0022020-02-02 14:45:0032020-02-03 11: 20:0042020-02-04 18:10:00

order_id	order_date
1

We want to The date portion of all orders is extracted.

You can use the following SQL statement to achieve this:

SELECT order_id, SUBSTRING(order_date, 1, 10) AS order_date

FROM orders;

Run the above SQL statement After that, we will get the following result:

order_idorder_date1 2020-02-0122020-02-0232020-02-0342020-02-04We use The SUBSTRING function extracts the first 10 characters of the order_date field, which is the date part.

Example 2: Extract summary from long text

Suppose we have the following data table "articles":

article_idtitlecontent##123We want to extract a summary of length 50 from the content field.

How to use the SUBSTRING function	MySQL SUBSTRING function Used to extract part of a string. This article will introduce how to use the SUBSTRING function.
MySQL Commonly Used Functions	MySQL provides many commonly used functions, such as SUBSTRING, CONCAT, LENGTH, etc.
Quick Start MySQL	This article will help you quickly get started with MySQL and learn basic add, delete, modify, and query operations.

You can use the following SQL statement to achieve this:

SELECT article_id, title, SUBSTRING(content, 1, 50) AS summary

FROM articles;

Run the above After the SQL statement, we will get the following results:

article_id123We use the SUBSTRING function to extract the first 50 characters of the content field, which is the summary part.

title	summary
How to use the SUBSTRING function	The MySQL SUBSTRING function is used to extract a part of a string.
Complete list of commonly used MySQL functions	MySQL provides many commonly used functions, such as SUBSTRING, CONCA
Quick Start MySQL	This article will help you quickly get started with MySQL and learn basic add, delete, modify and query operations.

Through the above examples, we can see the flexibility and practicality of the SUBSTRING function. It can be used in various scenarios to extract part of a string. Whether it is extracting the date part from a datetime string or extracting a summary from a long text, the SUBSTRING function can help us achieve these needs easily.

The above is an introduction to how to use the SUBSTRING function to extract part of a string in MySQL. Hope this helps!

The above is the detailed content of How to use the SUBSTRING function to extract part of a string in MySQL. For more information, please follow other related articles on the PHP Chinese website!