How to use MySQL8 LPAD() String Function?

by · Published · Updated

This function helps in adding new characters at the start of the string. We can add the left part of a string with one or more characters.

LPAD() functions return the string, left-padded with the string padstring to length upto the length specified. If a string is longer than length, the return value is shortened to length characters.

LPAD() : Syntax

LPAD ( string , length, padstring);

LPAD() : Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|lef|lef|left”]
Name, Required /Optional, Value Type, Description
string , Required, String , The input string to be padded.
length, Required, Numeric , The length of new string to be padded.
padstring, Required, String , The new string of characters is to be padded.
[/table]

LPAD() : Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Returns,
String, returns a new padded string .
[/table]

LPAD() : Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 4.0
[/table]

LPAD() Example 1 : Basic usage

Below is an example of left padding with the asterisk character:

mysql> SELECT LPAD('tutorialmines', 16, '*');
+---------------------------------+
| LPAD('tutorialmines', 16, '* ') |
+---------------------------------+
| ***tutorialmines                |
+---------------------------------+
1 row in set (0.00 sec)

Below is an example of left padding with the an exclamation mark:

mysql> SELECT LPAD('Tuts ', 15, '!');
+------------------------+
| LPAD('Tuts ', 15, '!') |
+------------------------+
| !!!!!!!!!!Tuts         |
+------------------------+
1 row in set (0.06 sec)

LPAD() Example 2 : Multiple Characters

Below is an example of left padding with the 2 characters:

mysql> SELECT LPAD('tutorialmines', 16, '* !');
+----------------------------------+
| LPAD('tutorialmines', 16, '* !') |
+----------------------------------+
| * !tutorialmines                 |
+----------------------------------+
1 row in set (0.00 sec)

LPAD() Example 3 : Second Argument is Too Small

If the value of the second argument i.e. length parameter is too small, you might end up with no padding. Below is an example of it.

mysql> SELECT LPAD('tutorialmines', 12, '* ');
+---------------------------------+
| LPAD('tutorialmines', 12, '* ') |
+---------------------------------+
| tutorialmine                    |
+---------------------------------+
1 row in set (0.00 sec)

LPAD() : Example 4 : NULL  arguments

It will return NULL, if any of the argument is NULL. Let’s see the below example.

mysql> SELECT LPAD(NULL, 12, '* ') as output1,
              LPAD('tuts', NULL, '* ') as output2,
              LPAD('tuts',5, NULL) as output3;
+---------+---------+---------+
| output1 | output2 | output3 |
+---------+---------+---------+
| NULL    | NULL    | NULL    |
+---------+---------+---------+
1 row in set (0.00 sec)

LPAD() : Example 5 : Use in DATABASE

Now, I am selecting data from a database, and padding it on its left side. Let’s see the below example.

mysql> SELECT LPAD(name,12,'*') from tbl_employee;
+-------------------+
| LPAD(name,12,'*') |
+-------------------+
| **Jyoti Rani      |
| *******Polla      |
| *******Arnav      |
| *****Jeevesh      |
| *********Tom      |
+-------------------+
5 rows in set (0.05 sec)

See all MySQL String functions MySQL 8 String Functions.

Related articles :BIT_LENGTH(), CHAR() , CHARACTER_LENGTH(), CHAR_LENGTH(), TRIM(), RTRIM(), RIGHT(), RPAD().

PHP Related articles : STR_PAD(), STR_REPEAT(), COUNT_CHARS(), STRSTR() ,STRCSPN() , STRCHR(), SUBSTR(), PHP STRING FUNCTIONS().

You may also like...