注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Memory extender

Beautiful Day..

 
 
 

日志

 
 

Instr函数的使用  

2011-08-10 16:01:45|  分类: SQL&PL/SQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
用以返回某个字符在某字符串中第几次出现的位置,返回位置数值
可以从左至右查询,亦可从右至左查询。

INSTR

Syntax

Description of instr.gif follows
Description of the illustration instr.gif

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 CHARVARCHAR2NCHARNVARCHAR2CLOB, 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 (ifsubstring does not appear occurrence times after the position character of string), then the return value is 0.

See Also:

Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

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 -----------------                27
  评论这张
 
阅读(452)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017