MySQL INSTR() function

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

INSTR() function

MySQL INSTR() takes a string and a substring of it as arguments, and returns an integer which indicates the position of the first occurrence of the substring within the string.

Syntax:

INSTR (ori_str, sub_str)

Arguments:

Name	Description
ori_str	The string to be searched.
sub_str	The string to be searched for within the ori_str.

Syntax Diagram:

MySQL Version: 5.6

Video Presentation:

Pictorial Presentation:

Example : MySQL INSTR() function

The following MySQL statement finds the first occurrence of 'st' in 'myteststring' at the position 5, it returns 5.

Code:

SELECT INSTR('myteststring','st');

Sample Output:

mysql> SELECT INSTR('myteststring','st');
+----------------------------+
| INSTR('myteststring','st') |
+----------------------------+
|                          5 | 
+----------------------------+
1 row in set (0.03 sec)

Example : MySQL INSTR() function with WHERE clause

The following MySQL statement returns a list of books (in the first column of the output) if string 'an' is found within the name of the book, and an integer (in the second column of the output) indicating the position of the first occurrence of the string 'an' within the name of the book. A condition placed after the WHERE clause makes sure that it returns only those books, within which, the position of the first occurrence of substring 'an' is more than 0.

Code:

SELECT book_name, INSTR(book_name,'an') FROM book_mast WHERE INSTR(book_name,'an')>0;

Sample table: book_mast

Sample Output:

mysql> SELECT book_name, INSTR(book_name,'an') FROM book_mast WHERE INSTR(book_name,'an')>0;
+-------------------------------------+-----------------------+
| book_name                           | INSTR(book_name,'an') |
+-------------------------------------+-----------------------+
| Understanding of Steel Construction |                     8 | 
| Transfer  of Heat and Mass          |                     3 | 
| Advanced 3d Graphics                |                     4 | 
| Human Anatomy                       |                     4 | 
| The Experimental Analysis of Cat    |                    18 | 
| Anatomy & Physiology                |                     1 | 
| Networks and Telecommunications     |                    10 | 
+-------------------------------------+-----------------------+
7 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-instr-function - php mysql examples | w3resource</title>
<meta name="description" content="example-instr-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><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 book_name,INSTR(book_name,"an") as output
FROM book_mast WHERE INSTR(book_name,"an")>0') as $row) {
echo "<tr>";
echo "<td>" . $row['book_name'] . "</td>";
echo "<td>" . $row['output'] . "</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-instr-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 book_name,INSTR(book_name,'an') as output FROM book_mast WHERE INSTR(book_name,'an')>0";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Book Name</td>
<td>Output</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("book_name")%></TD>
<TD><%=rs.getString("output")%></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: INSERT
Next: LCASE