w3resource
MYSQL Tutorial

MySQL INSERT() function

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.

MySQL Version: 5.6

Video Presentation

Pictorial Presentation

MySQL INSERT() 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>

All String Functions

MySQL String Functions, slide presentation


ankara escort
kızılay escort