MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL TIMESTAMPDIFF in MySQL MySQL Syntax Checker Sudo MySQL Secure Installation

MySQL INSTR() Function
2023-01-02 17:12:42

MySQL INSTR() Function

In this context, we will learn how we can use the MySQL INSTR() function with proper syntax and good examples.

Introduction of MySQL INSTR() function

This function in MySQL returns the location of a substring's first occurrence within a given string.

Sometimes, we want to locate a substring in a string or check if a substring exists in a string. In this case, we can use a built-in string function called INSTR.

The INSTR function returns the position of the first occurrence of a substring in a string. If the substring is not found in the str, the INSTR function returns zero (0).

The INSTR function is not case-sensitive. It means that it does not matter if we pass the lowercase, uppercase, title case, etc. The results are always same.

Suppose we want the INSTR function to perform searches in a case-sensitive manner on a non-binary string. In that case, we use the BINARY operator to cast one of the arguments of the INSTR function from a non-binary string to a binary string.

Syntax of the MySQL INSTR() function

The syntax of the MySQL INSTR() function is given as follows:

INSTR(string_I, string_II)

Parameters or arguments used in MySQL INSTR() function:

There are two parameters accepted by the INSTR() function in MySQL, which are given as follows:

string_I –

This is the string where the search function occurred.

string_II –

This is the string which will be searched in string I.

Returns:

It will return the position of the first occurrence of a substring within a given string.

Note: The function will return 0 if string_II is not found in string_I. INSTR() function only performs case-insensitive searches.

Application used for INSTR() function:

The INSTR() function can be used in the given below MySQL versions.:

  • MySQL 5.7
  • MySQL 5.6
  • MySQL 5.5
  • MySQL 5.1
  • MySQL 5.0
  • MySQL 4.1
  • MySQL 4.0
  • MySQL 3.23

Examples of MySQL INSTR() function:

Now, we will look into some MySQL INSTR() function examples and will explore how we can use the INSTR function in MySQL.

Example-1:

Here, we will derive the position of a sub-string.

SELECT INSTR("Python is a powerful Language", "powerful")
AS Found;

Output :

Found
13
1 row in set (0.00 sec)

Example-2:

This example shows how the INSTR() function is case-insensitive.

SELECT
INSTR("Python is a powerful Language", "IS")
AS 'Found1';
INSTR("Python is a powerful Language", "is")
AS 'Found2';

Output:

Found1  Found2
8               8
1 row in set (0.00 sec)

Example-3:

If string_2 is not found in string_1.

SELECT INSTR("Python is awesome", "hey")
AS Found;

Output:

Found
0
1 row in set (0.00 sec)

Example-4:

All possible errors in INSTR() function.

1) If only one parameter is passed.

SELECT INSTR("Python is a powerful Language")
AS 'Found';

Output:

Incorrect parameter count in the call to native function 'INSTR.'

2) If three or more parameters are passed.

SELECT INSTR("Python is a powerful Language", "is", "a", "lang)
AS 'Found';

Output:

Incorrect parameter count in the call to native function 'INSTR.'

Some more MySQL INSTR function examples

The statement below returns the substring MySQL's position in the MySQL INSTR string.

SELECT INSTR('MySQL INSTR', 'MySQL');

Output:

INSTR('MySQL INSTR',  'MySQL')
1

The statement given below returns the same result because the INSTR function is case-insensitive.

SELECT INSTR('MySQL INSTR', 'mysql');

To force the INSTR function to search based on case-sensitive fashion, we use the BINARY operator as follows:

SELECT INSTR('MySQL INSTR', BINARY 'MySQL');

Output:

INSTR('MySQL INSTR', BINARY 'mysql')
0

The result is different because MySQL vs MySQL now with the BINARY operator.

The INSTR function vs LIKE operator

We will use the commodity table in the sample database.

Commodity table

Let's consider we want to find a commodity whose name contains the car keyword. For this situation we are going to use the INSTR function as given below:

SELECT
commodity
FROM
commodity
WHERE
INSTR(commodity,'Car') > 0;

Output:

Commodity
1934 ford car
1978 indy 500 monte
18th century vintage horse
1917 maxwell touring car
1962 city Detroit streetcar

Besides the INSTR function, we can also use the LIKE operator to match the Car pattern.

SELECT
commodity
FROM
commodity
WHERE
commodity LIKE '%Car%';

Both queries return the same result. So which one is faster, the INSTR or the LIKE operator?

Answering this question, we can say that they are the same. They are both case-sensitive and perform full table scans.

Let's create an index on the commodity column.

CREATE INDEX idx_commodity_name ON commodity(commodity);

If we use the LIKE operator with the prefix search on this indexed column, the LIKE operator will perform faster than the INSTR function.

Let's visualize the statement given below.

SELECT
commodity
FROM
commodity
WHERE
commodity LIKE '1900%';

We can check it using the EXPLAIN statement, which is given below:

EXPLAIN SELECT
commodity
FROM
commodity
WHERE
commodity LIKE '1900%';

And compare with the following statement that uses the INSTR function.

EXPLAIN SELECT
commodity
FROM
commodity
WHERE
instr(commodity,'1900');

The INSTR function performs a table scan even though the commodity column has an index. This is because MySQL cannot make any assumption about the semantics of the INSTR function, whereby MySQL can utilize its understanding of the semantics of the LIKE operator.

The fastest way to test if a substring exists in a string is to use a full-text index. However, it is required a configure and maintain the index properly.

Application of MySQL INSTR() function:

This function is used to find the position of the first occurrence of a substring in a string.

Summary:

In the above context, we have learned how we can use the INSTR() function in MySQL used to find the position of the first occurrence of a substring in a string.