Question

value returned by INSTR function for an unsuccessful search ​

Answer 1

Answer:

The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence. INSTR calculates strings using characters as defined by the input character set. INSTRB uses bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.

position is an nonzero integer indicating the character of string where Oracle Database begins the search. If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.

occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive.

Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatype.

Both position and occurrence must be of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer. The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters. If the search is unsuccessful (if substring does not appear occurrence times after the position character of string), then the return value is 0.

Answer 2

What is the value returned by INSTR function when the search is unsuccessful?

Answer:

The value returned by the INSTR function when the search is unsuccessful is '0'.

Explanation:

• 'Whatever the value of the position', the return value is given in characters and is relative to the string's beginning.
• The return value is 0 if the search is unsuccessful (if the substring does not follow the character in the string's position times).
• Based on a count of substring occurrences, the INSTR function searches a character string for a given substring and returns the character position in that string where an occurrence of that substring terminates.
• The INSTR function determines a string's numeric starting place within a string.
• The SUBSTR function, for instance, returns the portion of the provided string that is indicated by the numeric character positions.
• With the ability to define the start position and which occurrence to search, Oracle's INSTR function provides the location of a substring within a string.
• In SQL Server, you can either use a user-defined function or the CHARINDEX function, which lets you provide the start position but not the occurrence.

#SPJ3