SQL Reference - STRPOS

STRPOS(string, substring) RETURNS integer

string: This is the source string where the search will occur. It is the text to be searched for the occurrence of the substring.
substring: This is the target string which will be searched in the source string. It is the text to look for within the source string.
returns integer: This parameter dictates that the return value of the function will be an integer. Specifically, it will return the starting position of the first occurrence of the substring within the string. If the substring is not found, the function will return 0.

Example

SELECT STRPOS('Computer Science', 'Science') as Position;

Output

 position
----------
 10
(1 row)

Explanation

In this STRPOS function example, the word ‘Science’ starts at position 10 in the string ‘Computer Science’. Thus, the function returns the value 10.

STRPOS(string, substring)

string: This is the string in which the search will be performed. The string can be clob, varchar, char, nvarchar, nchar, or long.
substring: This is the sequence of characters to be searched within the string. The substring can also be any of the above-mentioned data types.

Example

SELECT INSTR('hello world', 'world') FROM dual;

Output

Explanation

The INSTR function in Oracle is used to find the position of a substring in a string. It returns the location of the first character of the substring. In this case, it is searching for the position of ‘world’ in the string ‘hello world’, which is at position 6.