Instr Function

最新推荐文章于 2022-05-07 15:58:13 发布

原创最新推荐文章于 2022-05-07 15:58:13 发布 · 884 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#function #string #search #oracle

Oracle 专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍了 Oracle 数据库中 INSTR 函数的使用方法及其参数含义。INSTR 函数用于查找一个字符串在另一个字符串中的位置，包括指定起始位置及出现次数等高级功能。

学习是方法的问题，也是积累的过程。

Instr Function

In Oracle/PLSQL, the instr function returns the location of a substring in a string.

The syntax for the instr Oracle function is:

instr( string1, string2, [ start_position ], [ nth_appearance ] )

string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.

Note: If string2 is not found in string1, then the instr Oracle function will return 0.

For example:

instr('Tech on the net', 'e') would return 2; the first occurrence of 'e'
instr('Tech on the net', 'e', 1, 1) would return 2; the first occurrence of 'e'
instr('Tech on the net', 'e', 1, 2) would return 11; the second occurrence of 'e'
instr('Tech on the net', 'e', 1, 3) would return 14; the third occurrence of 'e'
instr('Tech on the net', 'e', -3, 2) would return 2.

instr('Tech on the net', 'e')	would return 2; the first occurrence of 'e'
instr('Tech on the net', 'e', 1, 1)	would return 2; the first occurrence of 'e'
instr('Tech on the net', 'e', 1, 2)	would return 11; the second occurrence of 'e'
instr('Tech on the net', 'e', 1, 3)	would return 14; the third occurrence of 'e'
instr('Tech on the net', 'e', -3, 2)	would return 2.