In SQL implementations that support the INSTR() function, what is the default…
2024
In SQL implementations that support the INSTR() function, what is the default starting index for the first character, and what value is returned if the specified substring is not found in the target string?
- A.
Starts from 1; returns NULL if not found
- B.
Starts from 0; returns -1 if not found
- C.
Starts from 1; returns -1 if not found
- D.
Starts from 0; returns 0 if not found
- E.
Starts from 1; returns 0 if not found
Attempted by 27 students.
Show answer & explanation
Correct answer: E
The INSTR() string function (in MySQL, Oracle, and similar SQL implementations) returns the position of the first occurrence of a substring within a string.
SQL strings are 1-indexed: the first character is at position 1 (not 0), so the default starting index returned for a match at the very beginning of the string is 1. By default the search begins at the first character. In MySQL, INSTR() takes only two arguments (the string and the substring) and has no start_position parameter; Oracle's INSTR() additionally accepts an optional start_position argument, which itself defaults to 1.
When the substring is not present in the target string, INSTR() returns 0 (zero). It does not return -1 (a 0-based convention used by some programming languages) or NULL (NULL is returned only when an argument itself is NULL).
Therefore the correct answer is: starts from 1, and returns 0 if the substring is not found.