Back to Function Index

LFIND()

Overview

LFIND() searches for a substring within another string and returns the character index of the specified occurrence, starting from the left side. If no occurrence number is specified, it returns the first occurrence. If the search string is not found or the specified occurrence exceeds the number of occurrences, LFIND() returns zero. Note that LFIND() is an alias for INSTR().

Function Format

LFIND(string, search string [, occurrence])

Return Value

LFIND() returns an integer

Examples

LFIND("Green apples taste better than red apples and all other types of apples.", "apples") = 7
    (first occurrence of "apples")

LFIND("Green apples taste better than red apples and all other types of apples.", "oranges") = 0
    (search string not found)

LFIND("Green apples taste better than red apples and all other types of apples.", "apples", 1) = 7
    (first occurrence)

LFIND("Green apples taste better than red apples and all other types of apples.", "apples", 2) = 36
    (second occurrence)

LFIND("Green apples taste better than red apples and all other types of apples.", "apples", 3) = 66
    (third occurrence)

LFIND("Green apples taste better than red apples and all other types of apples.", "apples", 4) = 0
    (no fourth occurrence)

LFIND("Hello World", "o") = 5
    (first occurrence of single character)

LFIND("", "test") = 0
    (empty string)

LFIND(NULL, "test") = NULL
    (NULL input)

Notes

• Important behaviors:
  • Returns 0 if search string is not found
  • Returns 0 if occurrence number exceeds available occurrences
  • Returns NULL if input string is NULL
  • Search is case-sensitive
  • Position counting starts at 1
• Common use cases include:
  • String pattern matching
  • Text analysis
  • Data validation
  • String parsing
  • Content extraction
• The function can be used with:
  • String literals
  • String columns
  • String expressions
  • String functions
• This function is often used in combination with other string functions like SUBSTR(), LEFT(), and RIGHT() for more complex string manipulations.
• For case-insensitive searches, consider converting both strings to the same case using UPPER() or LOWER().

See Also

• INSTR()
• SUBSTR()
• LEFT()
• RIGHT()