How the INSTR() function works in Mariadb?

The MariaDB INSTR() function is used to determine the position of the first occurrence of a substring within a string. It is commonly used in text processing where you need to find the location of a character or sequence of characters within a string.

Syntax

The syntax for the MariaDB INSTR() function is as follows:

INSTR(string, substring)

• string is the string to search.
• substring is the substring to search for in the string.

The function returns the position of the first occurrence of substring in string. If substring is not found, the function returns 0.

Examples

Example 1: Finding a Substring

This example shows how to find the position of a substring within a string.

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

The output for this statement is:

+--------------------------------+
| INSTR('Hello World!', 'World') |
+--------------------------------+
|                              7 |
+--------------------------------+

This result indicates that the substring ‘World’ starts at the 7th position in ‘Hello World!’.

Example 2: Substring Not Found

Demonstrating the return value when the substring is not found.

SELECT INSTR('Hello World!', 'MariaDB');

The output for this statement is:

+----------------------------------+
| INSTR('Hello World!', 'MariaDB') |
+----------------------------------+
|                                0 |
+----------------------------------+

Since ‘MariaDB’ is not found within ‘Hello World!’, the function returns 0.

Example 3: Case Sensitivity

Showing that the INSTR() function is case-sensitive.

SELECT INSTR('Hello World!', 'hello');

The output for this statement is:

+--------------------------------+
| INSTR('Hello World!', 'hello') |
+--------------------------------+
|                              1 |
+--------------------------------+

The function returns 1 because ‘hello’ (in lowercase) is found in ‘Hello World!’ (which has ‘Hello’ with an uppercase ‘H’).

Example 4: Numeric Substring

Finding the position of a numeric substring within a string.

SELECT INSTR('Item 25, Item 35, Item 45', '25');

The output for this statement is:

+------------------------------------------+
| INSTR('Item 25, Item 35, Item 45', '25') |
+------------------------------------------+
|                                        6 |
+------------------------------------------+

The numeric substring ‘25’ is found at the 6th position in the string.

Example 5: Using INSTR() with Table Data

Using INSTR() to search for a substring within strings stored in a table.

DROP TABLE IF EXISTS phrases;
CREATE TABLE phrases (sentence VARCHAR(255));
INSERT INTO phrases (sentence) VALUES ('Hello World!'), ('Welcome to MariaDB!');

SELECT sentence, INSTR(sentence, 'World') AS position FROM phrases;

The output for this statement is:

+---------------------+----------+
| sentence            | position |
+---------------------+----------+
| Hello World!        |        7 |
| Welcome to MariaDB! |        0 |
+---------------------+----------+

This table shows the sentences and the position of the substring ‘World’ within each sentence.

Related Functions

Below are a few functions related to the MariaDB INSTR() function:

• MariaDB LOCATE() function is used to find the position of a substring within a string, similar to INSTR().
• MariaDB SUBSTRING_INDEX() function is used to return a substring from a string before a specified number of occurrences of a delimiter.
• MariaDB LEFT() and RIGHT() functions are used to extract a specified number of characters from the left or right side of a string.

Conclusion

The INSTR() function in MariaDB is a straightforward and efficient way to locate the presence and position of a substring within a string. It is a valuable function for searching and manipulating text data within SQL queries. Understanding how to use INSTR(), along with its related functions, can greatly enhance text processing capabilities in MariaDB.