Mysql INSTR() function ~ Tech Blog

Tuesday, 10 July 2018

Mysql INSTR() function

Mysql INSTR()

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.

MySQL Version: 5.6

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');



Copy

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;



Copy

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>



Copy

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>

Tech Blog

Tuesday, 10 July 2018