Definition:
The Oracle INSTR function searches inside a string for a substring. The Oracle INSTR function works in a way similar to the SUBSTR function, but INSTR returns an integer number indicating theposition of the substring within the string, rather than returning the substring itself.
Note: For more complex string handling functions, you can make use of Regular Expressions by using the extended version of the INSTR function named REGEXPR_INSTR.
Example Syntax:
INSTR( source_string, substring [, start_position [, occurrence ] ] )
source_string is the string to be searched.
substring is the character string to be searched for inside of source_string.
start_position is an optional argument. It is an integer value that tells Oracle where to start searching in thesource_string. If the start_position is negative, then Oracle counts back that number of characters from the end of thesource_string and then searches backwards from that position. If omitted, this defaults to 1.
occurrence is an integer indicating which occurrence of substring Oracle should search for. That is, should INSTR return the first matching substring, the second matching substring, etc. This argument is optional. If omitted, it defaults to 1.
If the substring is not found in source_string, the Oracle INSTR function will return 0.
Notes:
Both source_string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, orNCLOB. The value returned is of NUMBER datatype.
Both start_position and occurrence must be an integer of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER.
The first position in the source_string is 1, not 0.
The INSTR search is case sensitive.
The Oracle INSTR function searches inside a string for a substring. The Oracle INSTR function works in a way similar to the SUBSTR function, but INSTR returns an integer number indicating theposition of the substring within the string, rather than returning the substring itself.
Note: For more complex string handling functions, you can make use of Regular Expressions by using the extended version of the INSTR function named REGEXPR_INSTR.
Example Syntax:
INSTR( source_string, substring [, start_position [, occurrence ] ] )
source_string is the string to be searched.
substring is the character string to be searched for inside of source_string.
start_position is an optional argument. It is an integer value that tells Oracle where to start searching in thesource_string. If the start_position is negative, then Oracle counts back that number of characters from the end of thesource_string and then searches backwards from that position. If omitted, this defaults to 1.
occurrence is an integer indicating which occurrence of substring Oracle should search for. That is, should INSTR return the first matching substring, the second matching substring, etc. This argument is optional. If omitted, it defaults to 1.
If the substring is not found in source_string, the Oracle INSTR function will return 0.
Notes:
Both source_string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, orNCLOB. The value returned is of NUMBER datatype.
Both start_position and occurrence must be an integer of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER.
The first position in the source_string is 1, not 0.
The INSTR search is case sensitive.
No comments:
Post a Comment