Skip to content

LPAD()

函数说明

函数 LPAD(str,len,padstr) 在字符串 str 左侧使用 padstr 进行填充,直至总长度为 len 的字符串,最后返回填充后的字符串。如果 str 的长度大于 len,那么最后的长度将缩减至 len
len 为负数,则返回 NULL。

函数语法

> LPAD(str,len,padstr)

参数释义

参数 说明
str 必要参数,被填充的字符串。CHAR 与 VARCHAR 类型均可。
len 必要参数,需要填充到的总长度。
padstr 必要参数,用于填充的字符串。CHAR 与 VARCHAR 类型均可。

示例

> drop table if exists t1;
> CREATE TABLE t1(Student_id INT,Student_name VARCHAR(100),Student_Class CHAR(20));
> INSERT INTO t1
VALUES
(1,'Ananya Majumdar', 'IX'),
(2,'Anushka Samanta', 'X'),
(3,'Aniket Sharma', 'XI'),
(4,'Anik Das', 'X'),
(5,'Riya Jain', 'IX'),
(6,'Tapan Samanta', 'X');
> SELECT Student_id, Student_name,LPAD(Student_Class, 10, ' _') AS LeftPaddedString FROM t1;
+------------+-----------------+------------------+
| Student_id | Student_name    | LeftPaddedString |
+------------+-----------------+------------------+
|          1 | Ananya Majumdar |  _ _ _ _IX       |
|          2 | Anushka Samanta |  _ _ _ _ X       |
|          3 | Aniket Sharma   |  _ _ _ _XI       |
|          4 | Anik Das        |  _ _ _ _ X       |
|          5 | Riya Jain       |  _ _ _ _IX       |
|          6 | Tapan Samanta   |  _ _ _ _ X       |
+------------+-----------------+------------------+
> SELECT Student_id, lpad(Student_name,4,'new') AS LeftPaddedString FROM t1;
+------------+------------------+
| Student_id | LeftPaddedString |
+------------+------------------+
|          1 | Anan             |
|          2 | Anus             |
|          3 | Anik             |
|          4 | Anik             |
|          5 | Riya             |
|          6 | Tapa             |
+------------+------------------+
> SELECT Student_id, lpad(Student_name,-4,'new') AS LeftPaddedString FROM t1;
+------------+------------------+
| Student_id | LeftPaddedString |
+------------+------------------+
|          1 | NULL             |
|          2 | NULL             |
|          3 | NULL             |
|          4 | NULL             |
|          5 | NULL             |
|          6 | NULL             |
+------------+------------------+
> SELECT Student_id, lpad(Student_name,0,'new') AS LeftPaddedString FROM t1;
+------------+------------------+
| Student_id | LeftPaddedString |
+------------+------------------+
|          1 |                  |
|          2 |                  |
|          3 |                  |
|          4 |                  |
|          5 |                  |
|          6 |                  |
+------------+------------------+