oracle 技巧补充

本文详细介绍了Oracle数据库中INSTR函数的使用方法,包括其语法、参数含义及返回值解释,并通过具体实例展示了如何进行字符串搜索定位。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

INSTR

Syntax

Description of instr.gif follows

Purpose

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.

Examples

The following example searches the string CORPORATE FLOOR , beginning with the third character, for the string "OR ". It returns the position in CORPORATE FLOOR at which the second occurrence of "OR " begins:

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)
  "Instring" FROM DUAL;
 
  Instring
----------
        14

In the next example, Oracle counts backward from the last character to the third character from the end, which is the first O in FLOOR . Oracle then searches backward for the second occurrence of OR , and finds that this second occurrence begins with the second character in the search string :

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)
"Reversed Instring"
     FROM DUAL;
 
Reversed Instring
-----------------
               2

The next example assumes a double-byte database character set.

SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes"
   FROM DUAL;

Instring in bytes
-----------------
               
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值