MySQL LPAD() function
LPAD() function
MySQL LPAD() left pads a string with another string. The actual string, a number indicating the length of the padding in characters (optional) and the string to be used for left padding - all are passed as arguments.
Syntax:
LPAD(str, len, padstr)
Arguments:
Name | Description |
---|---|
str | Actual string. |
len | A number indicating the total length of the string ( in characters) returned after padding. |
padstr | String which is used for left padding. |
MySQL Version: 5.6
Video Presentation:
Pictorial Presentation:
Example : MySQL LPAD() function
In the following MySQL statement, the first argument specifies a string of 6 characters, the second argument specifies that the length of the string returned after padding will be 10 characters and the third argument specifies the string to be used for left padding. So, 4 characters (10-6) are used for left padding and the function thus returns '****Hellow.
Code:
SELECT LPAD('Hellow',10,'**');
Sample Output:
mysql> SELECT LPAD('Hellow',10,'**'); +------------------------+ | LPAD('Hellow',10,'**') | +------------------------+ | ****Hellow | +------------------------+ 1 row in set (0.03 sec)
Example of LPAD() function with less than the original string
The following MySQL statement returns 'Hell'. This happens because, the first argument has 6 characters, second argument 4 is the total number of characters after left padding and the third argument is the padding string. Since a total number of characters after padding is less than the total number of characters in the first argument, so to meet the condition, two characters are omitted from the actual string (i.e. the first argument).
Cdoe:
SELECT LPAD('Hellow',4,'**');
Sample Output:
mysql> SELECT LPAD('Hellow',4,'**'); +-----------------------+ | LPAD('Hellow',4,'**') | +-----------------------+ | Hell | +-----------------------+ 1 row in set (0.00 sec)
Example of LPAD() function using column of a table
The following MySQL statement returns all the rows from publisher table, with the value of column country left padded with the string ‘**’, while after padding, the length of the string returned will be a value obtained by subtracting the length of the country () from 25.
Code:
SELECT LPAD(country,25-(length(country)),'**')
FROM publisher;
Sample table: publisher
Sample Output:
mysql> SELECT LPAD(country,25-(length(country)),'**') -> FROM publisher; +-----------------------------------------+ | LPAD(country,25-(length(country)),'**') | +-----------------------------------------+ | *******************USA | | ***************India | | *******Australia | | *********************UK | | *******************USA | | *******************USA | | *********************UK | | ***************India | +-----------------------------------------+ 8 rows in set (0.00 sec)
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-lpad-function - php mysql examples | w3resource</title>
<meta name="description" content="example-lpad-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>A list of the expression named as output. Where output is : left padded the column country with the string "**", after padding, the length of the string will be the subtraction of field size and the length of the content of the field.:</h2>
<table class='table table-bordered'>
<tr>
<th>Output</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT LPAD(country,25-(length(country)),"**") as output
FROM publisher') as $row) {
echo "<tr>";
echo "<td>" . $row['output'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
JSP script:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-lpad-function</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT LPAD(country,25-(length(country)),'**') as output FROM publisher";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Output</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("output")%></TD>
</TR>
<% } %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Cant connect to database.");
}
%>
</body>
</html>
Example of LPAD() function with where clause
The following MySQL statement returns all the rows from publisher table who is having the country name beginning with the letter ‘U’ and column country left padded with the string ‘**’. After padding, the length of the string will be the subtraction of field size and the length of the content of the field.
Code:
SELECT LPAD(country,25-(length(country)),'**')
FROM publisher
WHERE LEFT(country,1)=’U’;
Sample table: publisher
Sample Output:
mysql> SELECT LPAD(country,25-(length(country)),'**') -> FROM publisher -> WHERE LEFT(country,1)='U'; +-----------------------------------------+ | LPAD(country,25-(length(country)),'**') | +-----------------------------------------+ | *******************USA | | *********************UK | | *******************USA | | *******************USA | | *********************UK | +-----------------------------------------+ 5 rows in set (0.00 sec)
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example1-lpad-function - php mysql examples | w3resource</title>
<meta name="description" content="example1-lpad-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>A list of the expression named as output. Where output is the list of the countries who is having the country name beginning with the letter ‘U’ and with left padded the column country with the string ‘**’. After padding, the length of the string, will be the subtraction of field size and the length of the content of the field:</h2>
<table class='table table-bordered'>
<tr>
<th>Output</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT LPAD(country,25-(length(country)),"**") as output
FROM publisher
WHERE LEFT(country,1)="U"') as $row) {
echo "<tr>";
echo "<td>" . $row['output'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
Online Practice Editor:
All String Functions
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises