MySQL INSTR() Function

Introduction

In this chapter, we will learn about the INSTR() function in MySQL. The INSTR() function is used to find the position of the first occurrence of a substring within a string. It is a useful function for searching and locating specific text within a larger string. We will cover the syntax for the INSTR() function, examples of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the INSTR() function in MySQL is:

SELECT INSTR(string, substring);

• string: The string to be searched.
• substring: The substring to search for within the string.

Examples

Let’s go through examples of using the INSTR() function with various use cases.

1. Find the Position of a Substring in a String

Example: Find the Position of ‘World’ in ‘Hello, World!’

SELECT INSTR('Hello, World!', 'World') AS position;

Output:

2. Using INSTR() with Table Data

Assuming we have an employees table:

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

INSERT INTO employees (first_name, last_name, email) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com'),
('Priya', 'Singh', 'priya.singh@example.com'),
('Amit', 'Kumar', 'amit.kumar@example.com');

Example: Find the Position of ‘@’ in Email Addresses

SELECT email, INSTR(email, '@') AS at_position
FROM employees;

Output:

3. Using INSTR() with WHERE Clause

Example: Find Employees with ‘example’ in Their Email

SELECT first_name, last_name, email
FROM employees
WHERE INSTR(email, 'example') > 0;

Output:

Full Example

Let’s go through a complete example where we create a database and table, insert data, and demonstrate the usage of the INSTR() function.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

INSERT INTO employees (first_name, last_name, email) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com'),
('Priya', 'Singh', 'priya.singh@example.com'),
('Amit', 'Kumar', 'amit.kumar@example.com');

2. Using INSTR()

Example: Find the Position of ‘.’ in Email Addresses

SELECT email, INSTR(email, '.') AS dot_position
FROM employees;

Output:

Example: Find Employees with ‘sharma’ in Their Email

SELECT first_name, last_name, email
FROM employees
WHERE INSTR(email, 'sharma') > 0;

Output:

Important Considerations

• Case Sensitivity: The INSTR() function is case-sensitive. To perform a case-insensitive search, you can use the LOWER() function to convert both the string and the substring to lowercase.

  SELECT email, INSTR(LOWER(email), 'example') AS position
  FROM employees;
  

• Performance: The INSTR() function can impact performance, especially on large datasets. Optimize your queries by indexing columns used in the search.

• Return Value: The INSTR() function returns the position of the first occurrence of the substring. If the substring is not found, it returns 0.

Conclusion

The INSTR() function in MySQL is used for finding the position of a substring within a string. This chapter covered the syntax for using the INSTR() function, provided examples of its usage, and discussed important considerations. By mastering the INSTR() function, you can efficiently search and locate text within your databases.