INSTR()

Function Description

The INSTR() function is also used to return the location where the substring first appears in a given string. This function is multibyte-safe, which means it is suitable for a variety of character encodings and can handle multibyte characters correctly (such as UTF-8-encoded characters).

The INSTR() function is mainly used to clean and convert data, for example, when you need to find specific substrings in text fields or split text fields based on specific characters. This is very useful for processing data containing some pattern or format (such as email addresses, phone numbers, etc.).

Regarding case processing, the INSTR() function is case sensitive only if at least one parameter is a binary string. That is, for non-binary strings, the INSTR() function is case-insensitive. However, if you want to make case-sensitive comparisons, you can use the BINARY keyword to convert the string to binary format.

For example:

SELECT INSTR(BINARY 'abc', 'A');

The above query will return 0 because in binary format, 'A' and 'a' are considered different characters.

Function Syntax

> INSTR(str,substr)

Parameter definition

Parameters	Description
str	Required parameters. string is the string to search for.
substr	Required parameters. substring is the string you are looking for.

Example

• Example 1

mysql> SELECT INSTR('foobarbar', 'bar');
+---------------------------+
| instr(foobarbar, bar) |
+---------------------------+
| 4 |
+---------------------------+
1 row in set (0.01 sec)

• Example 2

-- -- Find the first occurrence of 'o' in the string 'Hello World', and the INSTR function returns 5, because 'o' first occurrence of 'Hello World'
mysql> SELECT INSTR('Hello World', 'o');
+---------------------------+
| instr(Hello World, o) |
+---------------------------+
| 5 |
+---------------------------+
1 row in set (0.01 sec)

• Example 3

-- Create a table named t1 with two columns a and b of type VARCHAR
CREATE TABLE t1(a VARCHAR, b VARCHAR);

-- Insert three rows of data into table t1
INSERT INTO t1 VALUES('axa','x'),('ababa','qq'),('qwer','er');

-- Select each row from table t1 and use the INSTR function to find where the string in column a first appears
mysql> select instr(a,b) from t1;
+-----------------+
| instr(a, b) |
+-----------------+
| 2 |
| 0 |
| 3 |
+-----------------+
3 rows in set (0.01 sec)

-- Select each row from table t1 and use the INSTR function to find where NULL first appears in column a
-- Since NULL is an unknown value, this query will return NULL
mysql> select instr(a,null) from t1;
+---------------------+
| instr(a, null) |
+---------------------+
| NULL |
| NULL |
| NULL |
+---------------------+
3 rows in set (0.00 sec)