Back to Function Index

INSTR()

Overview

INSTR() searches for a substring within a string and returns the position of the specified occurrence. The search starts from the left side of the string, and if no occurrence number is specified, it returns the position of the first occurrence. This function is useful for string manipulation, text parsing, and pattern matching.

Function Format

INSTR(string, search string [, occurrence])

Return Value

INSTR() returns an integer representing the position of the substring, or 0 if not found

Examples

INSTR("Green apples taste better than red apples and all other types of apples.", "apples")     = 7   (first occurrence)
INSTR("Green apples taste better than red apples and all other types of apples.", "oranges")    = 0   (not found)
INSTR("Green apples taste better than red apples and all other types of apples.", "apples", 1)  = 7   (first occurrence)
INSTR("Green apples taste better than red apples and all other types of apples.", "apples", 2)  = 36  (second occurrence)
INSTR("Green apples taste better than red apples and all other types of apples.", "apples", 3)  = 66  (third occurrence)
INSTR("Green apples taste better than red apples and all other types of apples.", "apples", 4)  = 0   (no fourth occurrence)
INSTR("Hello World", "o")                                                                      = 5   (first 'o')
INSTR("Hello World", "o", 2)                                                                   = 8   (second 'o')

Notes

• Important behaviors:
  • Returns 0 if the search string is not found
  • Returns 0 if the specified occurrence number exceeds the number of occurrences
  • Search is case-sensitive
  • Position counting starts at 1 (not 0)
• Common use cases include:
  • String parsing and extraction
  • Pattern matching
  • Text analysis
  • Data validation
  • String manipulation
• The function can be used with:
  • String literals
  • String columns
  • String expressions
  • String functions
• This function is an alias for LFIND().
• For case-insensitive searches, consider using UPPER() or LOWER() on both the string and search string.
• The function is particularly useful when combined with other string functions like SUBSTR() for text extraction.

See Also

• LFIND()
• SUBSTR()
• CONTAINS()
• LENGTH()