MySQL INSERT() function

Last update on April 04 2022 13:17:43 (UTC/GMT +8 hours)

INSERT() function

MySQL INSERT() function inserts a string within a string, removing a number of characters from the original string.

The original string, the string which will be inserted, a position of insertion within the original string and number of characters to be removed from the original string - all are specified as arguments of the function.

Syntax:

INSERT (ori_string, in_pos, length, new_string)

Arguments

Name	Description
ori_string	Original string.
in_pos	Position of insertion within the original string.
length	Number of characters to be removed from the original string.
new_string	The string to be inserted.

Syntax Diagram:

MySQL Version: 5.6

Video Presentation:

Pictorial Presentation:

Example of MySQL INSERT() function

In the following MySQL statement, string ' insert ' is inserted into the string 'Originalstring', removing 5 characters from the 4th character of the 'Originalstring'. The output is "Ori insert string".

Code:

SELECT INSERT('Originalstring', 4, 5, ' insert ');

Sample Output:

mysql> SELECT INSERT('Originalstring', 4, 5, ' insert ');
+--------------------------------------------+
| INSERT('Originalstring', 4, 5, ' insert ') |
+--------------------------------------------+
| Ori insert string                          | 
+--------------------------------------------+
1 row in set (0.02 sec)

Example of MySQL INSERT() function with negative value

The following MySQL statement returns Originalstring, the actual string itself. This happens because the position of insertion, which is specified as -3, is out of range, so no insertion takes place.

Code:

SELECT INSERT('Originalstring', -3, 5, ' insert ');

Sample Output:

mysql> SELECT INSERT('Originalstring', -3, 5, ' insert ');
+---------------------------------------------+
| INSERT('Originalstring', -3, 5, ' insert ') |
+---------------------------------------------+
| Originalstring                              | 
+---------------------------------------------+
1 row in set (0.02 sec)

Example of MySQL INSERT() function exceeded length

The following MySQL statement returns "Ori insert". This happens because the number of characters to be removed (from the 4th position of the original string) exceeds the number of characters available (after the 4th position) in the original string. So it continues to remove the characters untill the end of the original string.

Code:

SELECT INSERT('Originalstring', 4,15, ' insert ');

Sample Output:

mysql> SELECT INSERT('Originalstring', 4,15, ' insert ');
+--------------------------------------------+
| INSERT('Originalstring', 4,15, ' insert ') |
+--------------------------------------------+
| Ori insert                                 | 
+--------------------------------------------+
1 row in set (0.00 sec)

Example of MySQL INSERT() function using where

The following MySQL statement takes the aut_id from the author table checking if the country of the author is USA, if so, then it returns a string by inserting a new string ‘/’ at the 4th position (of the aut_id), removing 0 number of characters from the 4th position (of the aut_id).

Code:

SELECT INSERT(aut_id,4,0, '/')
FROM author 
WHERE country='USA';

Sample table: author

Sample Output:

mysql> SELECT INSERT(aut_id,4,0, '/')
    -> FROM author 
    -> WHERE country='USA';
+-------------------------+
| INSERT(aut_id,4,0, '/') |
+-------------------------+
| AUT/006                 | 
| AUT/008                 | 
| AUT/010                 | 
| AUT/015                 | 
+-------------------------+
4 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">
<title>example-insert-function - php mysql examples | w3resource</title>
<meta name="description" content="example-insert-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>list of author's id with a string manipulation function called insert:</h2>
<table class='table table-bordered'>
<tr>
<th>Author's id</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 INSERT(aut_id,4,0, "/") as a_id 
FROM author
WHERE country="USA"') as $row) {
echo "<tr>";
echo "<td>" . $row['a_id'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

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-insert-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 INSERT(aut_id,4,0, '/') as a_id FROM author WHERE country='USA'";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Author's id</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("a_id")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Cant connect to database.");
}
%>
</body>
</html>

Online Practice Editor:

All String Functions

Previous: HEX
Next: INSTR