INSTR() function in SQL

Last Updated : 8 Sep, 2026

The INSTR() function in SQL is a string function used to find the position of a substring within a string. It returns the position of the first occurrence of the specified substring.

  • Useful for searching text within a string.
  • Returns the position of the first occurrence of the specified substring.
  • If the substring is not found, it returns 0.

Syntax

INSTR(input_string, search_string)
  • input_string: The string in which you want to search.
  • search_string: The substring whose position you want to find.

Examples

Example 1: Find the Position of a Character

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

Output:

Screenshot-2026-09-02-152436
  • Searches for the character W.
  • Returns its position in the string.

Example 2: Search for a Substring

SELECT INSTR('Learn SQL Programming', 'Programming') AS position;

Output:

sql_output
  • Searches for Programming in the given string.
  • Returns 11, the starting position of Programming.

Example 3: Use INSTR() on a Table Column

Let us create an employees table containing employee names.

Screenshot-2026-09-02-152717

Now, we can find the position of the space in each employee name.

SELECT employee_name,
       INSTR(employee_name, ' ') AS space_position
FROM employees;

Output:

Screenshot-2026-09-02-153006
  • Finds the position of the first space in each name.
  • Returns the position as space_position.

Note: MySQL and Oracle support the INSTR() function. SQL Server does not support INSTR(); it uses the CHARINDEX() function instead.

Comment

Explore