Use MySQL's LPAD function to fill the left side of the string with specified characters

Title: Use of MySQL's LPAD function to fill specified characters on the left side of a string

Introduction:
In MySQL, the LPAD function can conveniently fill specified characters on the left side of a string. This operation is very useful in many practical scenarios, such as ensuring the length consistency of strings or beautifying the output results. This article will introduce the usage of LPAD function and give some practical code examples.

1. Syntax and parameters of LPAD function
The syntax of LPAD function is as follows:
LPAD(str, len, padStr)

Among them, str represents the string to be filled; len represents the length of the filled string; padStr represents the characters used for padding. The return value of the LPAD function is the padded string.

2. Examples of using the LPAD function

1. Example 1: Filling characters of a specified length
   Suppose we have a string 'abc' and need to fill it It is a string of 10 characters in length, and the fill character is '-':

   SELECT LPAD('abc', 10, '-') AS result;

   After executing the above SQL statement, the result will be '-------abc'.

2. Example 2: Filling a number with a specified length
   Suppose we have a number 10 and need to fill it into a 5-digit string. The filling character is '0':

   SELECT LPAD(10, 5, '0') AS result;

   After executing the above SQL statement, the result will be '00010'.

3. Example 3: Maintain string length consistency
   Suppose we have a string table with a column of 'gender', and now we need to add all the values ​​of this column The padding is 5 characters in length, and the padding character is ' ' (space) to maintain the consistency of the string length:

   UPDATE table_name SET gender = LPAD(gender, 5, ' ');

   After executing the above SQL statement, all values ​​in the 'gender' column will be filled with 5 characters in length.

4. Example 4: Beautify the result output
   Suppose we have a query result set, in which a certain column is 'student_name', and the value of this column needs to be filled in when the result is output. 10 characters in length, the padding character is ' ' (space):

   SELECT LPAD(student_name, 10, ' ') AS result FROM table_name;

   After executing the above SQL statement, all values ​​in the 'student_name' column will be filled to 10 characters in length and output as the result.

Conclusion:
MySQL's LPAD function can easily fill the left side of the string with specified characters to maintain string length consistency or beautify the result output. Through the introduction and examples of this article, readers can deepen their understanding of the LPAD function and flexibly use this function in actual development.

Reference materials:

1. MySQL official documentation: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html
2. MySQL tutorial-novice tutorial: https://www.runoob.com/mysql/mysql-lpad.html

The above is the detailed content of Use MySQL's LPAD function to fill the left side of the string with specified characters. For more information, please follow other related articles on the PHP Chinese website!