MYSQL Tutorial

MySQL INSTR() function

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.


INSTR (ori_str, sub_str)


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

Syntax Diagram:

MySQL INSTR() Function - Syntax Diagram

MySQL Version: 5.6

Video Presentation

Pictorial Presentation

MySQL INSTR function

Example : MySQL INSTR() function

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


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.


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">
<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">
<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'>
<th>Author's id</th><th>Output</th>
$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>";

View the example in browser

JSP script

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
try {
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>
while (rs.next()) {
<%   }    %>
} catch (Exception ex) {
out.println("Cant connect to database.");

All String Functions

MySQL String Functions, slide presentation

Many of our valued users post comments along with piece of code. Disqus may, false positively, consider those piece of code as Bad or Starnge syntax and send those comments to spam automatically. Even if that happens, we will make sure those useful comments are taken out of spam and approved. It may take a couple of days for that though, but any useful comment will be brought to public view for sure. We regret if that happend to any user. You may write us directly regarding this to - w3resource[at]yahoo[dot]com.