INSTR
The INSTR (“in string”) function searches for a character or substring within a string and returns an integer for the location if that string is found, or zero if it is not found. The first argument, which can be a literal string, a variable, or a table column, is searched for the string specified by the second argument. If the string is found within the string, its position is returned as an integer relative to either the start or the end of the string.
Syntax
INSTR (string_to_examine, string_to_find[, search_start[, nth_occurrence]])
Remarks
• The first argument, string_to_examine, can be a literal expression or variable name enclosed in single-quotes. The first argument can also be an expression within a SQL SELECT to evaluate the values within a tableName.columnName. The data type must be VARCHAR or similar.
• The second argument, string_to_find, should be a string, or a variable with a data type of VARCHAR.
• Optionally, you can specify search_start to make the search proceed from any arbitrary position within the string.
• If the search proceeds from the end of string_to_examine, the result is always 0.
• If INSTR is executed in TDV, it returns NULL for INSTR('','C') and 0 for INSTR(' ','C'). When pushed to some databases, INSTR('','C') might return 0 as opposed to NULL.
Note: The difference is a space character. The C character is just an example.
• INSTR treats empty strings as NULL.
• The location of any substring match is reported with a count that starts with the first character position on the left.
• The INSTR function can be used to parse a concatenated value to identify the spaces between space-delimited names or words.
• Each leading space counts as one character.
Note: See also the related function
POSITION.
Examples
INSTR (' jean_doe', ' ', 2, 1)
This sample INSTR function call (with one leading space) returns 6.
INSTR (' jean_doe', ' ', 2, 1)
This sample INSTR function call (with two leading spaces) returns 2.