函数SUBSTR和INSTR有什么区别？

SUBSTR和INSTR都是字符函数，SUBSTR是截取子串，而INSTR的作用是获取指定字符的位置。

1、SUBSTR：截取子串

下标从1开始，这个函数有三个参数，第一个参数为目标字符串，第二个参数是将要输出的子串的起点，第三个参数是将要输出的子串的长度，如果没有第三个参数，那么余下的字符全部输出。如果第二个参数为负数，那么将会从目标字符串的尾部开始向前定位至负数的绝对值的位置。下面给出一个例子：

SYS@raclhr1> SELECT SUBSTR('lihuarong',0,3) col_1,

  2              SUBSTR('lihuarong',1,3)col_2,

  3              SUBSTR('lihuarong',3)col_3,

  4              SUBSTR('lihuarong',-4,3) col_4

  5  FROM DUAL;

COL COL COL_3   COL

--- --- ------- ---

lih lih huarong ron

2、INSTR：相当于STRING类中的INDEXOF，求索引

如果需要知道在一个字符串中满足特定的内容的子串位置，那么可以使用INSTR，它的第一个参数是目标字符串，第二个参数是匹配的内容，第三和第四个参数是数字，用以指定开始搜索的起点以及指出第几个满足条件的将会被返回。默认第三个与第四个参数的数值均为1，如果第三个数值为负数，那么将会从后向前搜索。如果未匹配到，那么返回0。需要注意的是，无论是从前向后还是从后向前搜索，返回的数值都是从左到右的数值。

SYS@raclhr1> SELECT INSTR('HelloWHoldhH', 'H', 6, 2) l1, --从第6个位置开始，第2次出现H的位置

  2         INSTR('HelloWHoldhH', 'H') l2, --从第1个位置开始，第1次出现H的位置

  3         INSTR('HelloWHoldhH', 'H', 2) l3, --从第2个位置开始，第1次出现H的位置

  4         INSTR('HelloWHoldhH', 'H', -1) l4, --从倒数第1个位置开始从右往左搜索，第1次出现H的位置

  5         INSTR('HelloWHoldhH','/')  l5--从第1个位置开始，第1次出现H的位置，如果匹配不到那么返回0

  6  FROM   DUAL;

        L1         L2         L3         L4         L5

---------- ---------- ---------- ---------- ----------

        12          1          7         12          0

3、INSTR的特殊用法

下面的2条SQL语句的功能是一样的：

SELECT * FROM DBA_OBJECTS D WHERE  INSTR('5,6,7', D.OBJECT_ID) > 0;

SELECT * FROM DBA_OBJECTS D WHERE D.OBJECT_ID=5 OR D.OBJECT_ID=6 OR D.OBJECT_ID=7;

其结果为：

SQL> SELECT D.OBJECT_ID FROM DBA_OBJECTS D WHERE  INSTR('5,6,7',D.OBJECT_ID) > 0;

 OBJECT_ID

----------

         7

         6

         5

4、SUBSTR和INSTR的联合使用

SUBSTR和INSTR这2个函数常常关联使用，但是如果INSTR匹配不到字符串的话，那么返回的就是0，这样SUBSTR得到的值就是空值，所以，这个时候就应该进行转换，使用DECODE或其它函数来转化。下面给出一个例子：

SELECT NAME,

       SUBSTR(NAME, 1, INSTR(NAME, 'a') - 1),

       DECODE(INSTR(NAME, 'a'),0, NAME,SUBSTR(NAME, 1, INSTR(NAME, 'a') - 1))

FROM   XB_B;

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

---------------优质麦课------------