Skip to main content
Documents
Share
LinkedIn
Facebook
Twitter
Copy
ClausesStatementsKeywordsExpressionsFunctionsIndexesPerformance Optimization

MySQL INSTR() Function

The `INSTR()` function in MySQL returns the position of the first occurrence of a substring within a string. It is commonly used to locate substrings in larger text fields.

Usage

The `INSTR()` function is used when you need to determine the position of a substring within another string. It helps in tasks like validating input, parsing strings, or filtering data based on the presence of specific substrings.

sql
INSTR(string, substring)

In this syntax, `string` is the main text where you search for `substring`, and the function returns the position (1-based index) of the first occurrence.

Examples

1. Basic Usage

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

In this example, `INSTR()` returns `7` because "World" starts at the 7th position in "Hello World".

2. Case Sensitivity

sql
SELECT INSTR('Database Management Systems', 'manage') AS position;

This will return `0` because `INSTR()` is case-sensitive and the substring "manage" does not match exactly.

3. Use in WHERE Clause

sql
SELECT title
FROM books
WHERE INSTR(title, 'SQL') > 0;

This query retrieves book titles containing the substring "SQL", as `INSTR()` returns a positive value if the substring is found.

Tips and Best Practices

  • Be mindful of case sensitivity. `INSTR()` is case-sensitive; convert strings to a uniform case using `LOWER()` or `UPPER()` if necessary.
  • Use with care in large datasets. Frequent use of `INSTR()` on large text fields can impact performance; consider indexing or limiting the dataset. For improved performance, especially in large text searches, consider using full-text indexes.
  • Check for zero result. A result of `0` indicates the substring was not found; use this to handle conditional logic in queries.
  • Combine with string functions. Use `INSTR()` in combination with `SUBSTRING()` or `REPLACE()` for string manipulation tasks.
  • Handle empty substrings. If the substring is empty, `INSTR()` returns `1`, which might not be intuitive. Be prepared to handle this scenario in your queries.
  • Compare with `LOCATE()`. The `INSTR()` function is similar to `LOCATE()`, but they differ in parameter order. While `INSTR()` takes `string` and then `substring`, `LOCATE()` accepts `substring` before `string`. Choose the function that aligns with your preference or existing codebase.