INSTR()

Description

INSTR() function is used to return the position of the first occurrence of a substring in a given string. This function is multibyte safe, which means it is compatible with various character encodings and can correctly handle multibyte characters (such as characters encoded in UTF-8).

The INSTR() function primarily uses data cleaning and transformation, such as when you need to search for a specific substring within a text field or split a text field based on a specific character. This can be particularly useful when dealing with data that follows a specific pattern or format, like email addresses, phone numbers, etc.

Regarding case sensitivity, INSTR() function is case-sensitive only if at least one argument is a binary string. For non-binary strings, INSTR() is case-insensitive. However, to make a case-sensitive comparison, you can use the BINARY keyword to cast the string into a binary format.

For example:

  1. SELECT INSTR(BINARY 'abc', 'A') AS Match;

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

Syntax

  1. > INSTR(str,substr)

Arguments

ArgumentsDescription
strRequired. The string is the string to search in.
substrRequired. The substring is the string you are looking for.

Examples

  • Example 1
  1. mysql> SELECT INSTR('foobarbar', 'bar');
  2. +-----------------------+
  3. | instr(foobarbar, bar) |
  4. +-----------------------+
  5. |                     4 |
  6. +-----------------------+
  7. 1 row in set (0.01 sec)
  • Example 2
  1. -- Using the INSTR function to find the first occurrence of 'o' in the string 'Hello World' will return 5, as 'o' first appears at the 5th position in 'Hello World'.
  2. mysql> SELECT INSTR('Hello World', 'o');
  3. +-----------------------+
  4. | instr(Hello World, o) |
  5. +-----------------------+
  6. |                     5 |
  7. +-----------------------+
  8. 1 row in set (0.01 sec)
  • Example 3
  1. -- Create a table named t1, which contains two VARCHAR type columns a and b
  2. CREATE TABLE t1(a VARCHAR, b VARCHAR);
  4. -- Insert three rows of data into the table t1
  5. INSERT INTO t1 VALUES('axa','x'),('abababa','qq'),('qwer','er');
  7. -- Select each row from table t1, then use the INSTR function to find the position at which the string in column b first appears in column a
  8. mysql> select instr(a,b) from t1;
  9. +-------------+
  10. | instr(a, b) |
  11. +-------------+
  12. |           2 |
  13. |           0 |
  14. |           3 |
  15. +-------------+
  16. 3 rows in set (0.01 sec)
  18. -- Select each row from table t1, then use the INSTR function to find the position at which NULL first appears in column a
  19. -- Since NULL is an unknown value, this query will return NULL
  20. mysql> select instr(a,null) from t1;
  21. +----------------+
  22. | instr(a, null) |
  23. +----------------+
  24. |           NULL |
  25. |           NULL |
  26. |           NULL |
  27. +----------------+
  28. 3 rows in set (0.00 sec)