数据中字符串的截取substr、查找instr、替换replace

最新推荐文章于 2023-03-31 17:56:16 发布

cuimiwan3140

最新推荐文章于 2023-03-31 17:56:16 发布

阅读量680

点赞数

文章标签：数据库

1.截取字符串：substr

SUBSTR返回字符串的一部分，在指定的字符位置开始并延伸指定的字符数。

SUBSTR计算使用的汉字输入的字符集定义的长度。

SUBSTRB使用字节而不是字符。

SUBSTRC使用Unicode字符完成。

返回值：

CHAR | NCHAR | VARCHAR2 | NVARCHAR2

语法：

{ SUBSTR | SUBSTRB | SUBSTRC }（CHAR，position [，substring_length]）

其中，char代表元字符串，position代表开始的位置，substring_length是可选项，表示截取的字符串的长度。

示列：

---- substr('ABCDEFG', 0); -- 返回结果是：ABCDEFG，从0位开始截取后面所有

SQL> select substr('ABCDEFG',0) from dual;

SUBSTR(

-------

ABCDEFG

---- substr('ABCDEFG', 2); -- 返回结果是：BCDEFG，从2位开始截取后面所有

SQL> select substr('ABCDEFG',2) from dual;

SUBSTR

------

BCDEFG

---- substr('ABCDEFG', 0, 2); -- 返回结果是：AB，从0位开始往后截取2个字符长度

SQL> select substr('ABCDEFG',0,2) from dual;

---- substr('ABCDEFG', 0, 10); -- 返回结果是：ABCDEFG，虽然10超出了元字符串长度，但是系统会按元字符串最大数量返回，不会影响返回结果

SQL> select substr('ABCDEFG',0,10) from dual;

SUBSTR(

-------

ABCDEFG

---- substr('ABCDEFG', -3); -- 返回结果是：EFG，如果是负数，则从尾部往前数，截取-3位置往后的所有字符串

SQL> select substr('ABCDEFG',-2) from dual;

SUB

---

2.查找字符串位置：instr

INSTR函数查找字符串的子字符串。该函数返回一个整数。

INSTR计算使用的汉字输入的字符集定义字符串。

INSTRB使用字节而不是字符。

INSTRC使用Unicode字符完成。

返回值

NUMBER

句法

{ INSTR | INSTRB | INSTRC } (string , substring [, position [, occurrence ] ])

其中sting是元字符串；substring是要查找的字符串；position是要查找的开始位置，是可选项，默认为1，所以，注意，在这里字符串搜索是从1开始的，如果此参数为正，则从左到右检索，如果此参数为负，则从右到左检索；occurrence是元字符串中第几次出现的子字符串，也是可选项，默认为1，如果是负数则报错。

示列：

---- instr('ABCDEFG', 'AB'); -- 返回结果是：1，因为instr字符串索引从1开始，所以是1不是0

SQL> select instr('ABCDEFG','AB') from dual;

INSTR('ABCDEFG','AB')

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

---- instr('CDEFGACAB','AB'); -- 返回结果是8，原因同上

SQL> select instr('CDEFGACAB','AB') from dual;

INSTR('CDEFGACAB','AB')

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

---- instr('ABCDabcdABCD','AB',1,2); -- 返回结果是：9，返回第二次出现'AB'的位置

SQL> select instr('ABCDabcdABCD','AB',1,2) from dual;

INSTR('ABCDABCDABCD','AB',1,2)

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

---- instr(' A BCDabcdABCD','AB',4,1); -- 返回结果是：11，从第4个字符开始查找第一次出现'AB'的位置。1个空格也算一个字符

SQL> select instr(' A BCDabcdABCD','AB',4,1) from dual;

INSTR('ABCDABCDABCD','AB',4,1)

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

3.替换字符串：replace

更换搜索的字符串，用另一个字符串替换它，并返回修改后的字符串。

返回值

CHAR | NCHAR | VARCHAR2 | NVARCHAR2

语法

REPLACE(char, search_string [, replacement_string ])

表示在char中查找search_string，replacement_string是替换search_string的字符串，如果没有该字符串，表示将search_string字符串删除。

示列：

---- replace('ABCDEF', 'CDE'); -- 返回结果是：ABF

SQL> select replace('ABCDEF','CDE') from dual;

REP

---

ABF

---- replace('ABCDEF', 'CDE', 'cdE'); -- 返回结果是：ABcdEF

SQL> select replace('ABCDEF','CDE','cdE') from dual;

REPLAC

------

ABcdEF

---- replace('ABCDEF', 'CDE', ' '); -- 返回结果是：AB F，CDE被替换成一个空格

SQL> select replace('ABCDEF','CDE',' ') from dual;

REPL

----

AB F

4.substr和instr相结合

----实现截取指定字符之间的字符串，常常将substr和instr相结合使用

----instr找到截取字符串位置，substr再截取

示列：

SQL> create table test(name varchar2(100));

SQL> insert into test values('abc-ABCdef-GH-IJKLM');

SQL> insert into test values('OPQ-rstN-uVW-XYZ');

----截取第三串字符（就是第二次出现“-”和第三次出现“-”之间的字符）

------思路：

SQL> select * from test;

NAME

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

abc-ABCdef-GH-IJKLM

OPQ-rstN-uVW-XYZ

--查找第二次出现"-"的位数

SQL> select instr(name,'-',1,2) from test;

INSTR(NAME,'-',1,2)

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

----查找第三次出现“-”的位置

SQL> select instr(name,'-',1,3) from test;

INSTR(NAME,'-',1,3)

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

----也就是说，需要截取的就是上面的位置，如：（注意找规律）

SQL> select substr(name,12,2) from test;

SUBS

----

W-

SQL> select substr(name,10,3) from test;

SUBSTR

------

f-G

uVW

----最后用instr和substr相结合：

SQL> select substr(name,instr(name,'-',1,2)+1,instr(name,'-',1,3)-instr(name,'-',1,2)-1) from test;

SUBSTR(NAME,INSTR(NAME,'-',1,2)+1,INSTR(NAME,'-',1,3)-INSTR(NAME,'-',1,2)-1)

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

uVW

----在比如：

select substr(name,instr(name,'《',1,1)+1,instr(name,'》',1,1)-instr(name,'《',1,1)-1) "书名",

substr(name,instr(name,'：',1,1)+1,instr(name,'出',-1,1) -instr(name,'：',1,1)-1) "作者",

substr(name,instr(name,'（',1,1)+1,instr(name,'）',1,1)-instr(name,'（',1,1)-1) "年份",

name

from books;

书名	作者	年份	NAME
oracle数据库性能优化	盖国强	2005	《oracle数据库性能优化》作者：盖国强出版（2005）
oracle编程艺术	Thomas Kyte	2011	《oracle编程艺术》作者：Thomas Kyte 出版（2011）

----上面查询作者时，instr(name,'出',-1,1)，用-1 从字符串右边开始读取的原因是，“出”右边不会有和他重复的字，如果还是从左边开始读取，如果书名中有“出”就会截取不到作者咯

5.update和replace相结合

----update和replace相结合可以实现批量更新

----示列：

select id,name,replace(name,'oracle','数据库') from books;

ID	NAME	REPLACE(NAME,'ORACLE','数据库'
1500	《oracle数据库性能优化》	《数据库数据库性能优化》
1552	《oracle编程艺术》	《数据库编程艺术》
1008	《我们都是oracle工程师》	《我们都是数据库工程师》
1009	《我接触过oracle》	《我接触过数据库》

update books set name=replace(name,'oracle','数据库') where id in (1500,1008,1009);

select * from books;

ID	NAME
1500	《数据库数据库性能优化》
1552	《oracle编程艺术》
1008	《我们都是数据库工程师》
1009	《我接触过数据库》

reference substr http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions078.htm#OLAXS471

instr http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions030.htm#OLAXS422

replace http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions065.htm#OLAXS458

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2123244/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2123244/

cuimiwan3140

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据中字符串的截取substr、查找instr、替换replace

1.截取字符串：substr SUBSTR返回字符串的一部分，在指定的字符位置开始并延伸指定的字符数。 SUBSTR计算使用的汉字输入的字符集定义的长度。 SUBST...
复制链接

扫一扫