南大通用GBase 8a字符串替换函数replace、insert、regexp_replace使用样例

本文介绍几个GBase 8a数据库里,用于字符串替换的几个函数用法和使用样例。

参考

GBase 8a字符串和日期函数列表索引

replace(str,from_str,to_str)

参数

  • str : 原始字符串
  • from_str : 被替换的字符串
  • to_str : 将from_str替换成这个字符串。

说明

是将 str 中所有出现的 from_str 替换为 to_str 后的字符串。

样例

gbase> select replace('1234567890','234','ABCD');
+------------------------------------+
| replace('1234567890','234','ABCD') |
+------------------------------------+
| 1ABCD567890                        |
+------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

替换多次

则都替换了

gbase> select replace('12345678901234567890','234','ABCD');
+----------------------------------------------+
| replace('12345678901234567890','234','ABCD') |
+----------------------------------------------+
| 1ABCD5678901ABCD567890                       |
+----------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

参数为NULl则返回NULL

gbase> select replace('12345678901234567890','234',null);
+--------------------------------------------+
| replace('12345678901234567890','234',null) |
+--------------------------------------------+
| NULL                                       |
+--------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select replace('12345678901234567890',null,'ABCD');
+---------------------------------------------+
| replace('12345678901234567890',null,'ABCD') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

insert(str,pos,len,newstr)

参数

  • str: 原始字符串
  • pos : 起始位置,从1开始。为0时功能无效。超过最大长度时,无效,返回原始字符串。
  • len : 替换几个字符,可以是0。长度超过末尾时,只替换到末尾。
  • newstr : 替换成这个字符串

说明

在字符串str中,从pos位置开始,选取len个字符长度的子串替换为字符串newstr。如果 pos 值不在长度范围之内,则返回原来的字符串;如果 len 值不在字符串剩余长度范围之内,则替换从 pos 位置开始的其余字符串;任何一个参数是 NULL,则返回 NULL。

样例

测试不同的pos起始位置

可以看到当pos为0时,没有做任何替换。

gbase> select insert('1234567890',0,3,'ABC');
+--------------------------------+
| insert('1234567890',0,3,'ABC') |
+--------------------------------+
| 1234567890                     |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select insert('1234567890',1,3,'ABC');
+--------------------------------+
| insert('1234567890',1,3,'ABC') |
+--------------------------------+
| ABC4567890                     |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select insert('1234567890',2,3,'ABC');
+--------------------------------+
| insert('1234567890',2,3,'ABC') |
+--------------------------------+
| 1ABC567890                     |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',3,3,'ABC');
+--------------------------------+
| insert('1234567890',3,3,'ABC') |
+--------------------------------+
| 12ABC67890                     |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select insert('1234567890',4,3,'ABC');
+--------------------------------+
| insert('1234567890',4,3,'ABC') |
+--------------------------------+
| 123ABC7890                     |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

当pos和长度不足时

字符串长度10,当最高末尾是11,等于在末尾追加了。

gbase> select insert('1234567890',9,3,'ABC');
+--------------------------------+
| insert('1234567890',9,3,'ABC') |
+--------------------------------+
| 12345678ABC                    |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',10,3,'ABC');
+---------------------------------+
| insert('1234567890',10,3,'ABC') |
+---------------------------------+
| 123456789ABC                    |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',11,3,'ABC');
+---------------------------------+
| insert('1234567890',11,3,'ABC') |
+---------------------------------+
| 1234567890ABC                   |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)

当pos+leng超过最大长度时

不做替换,返回原始字符串。

gbase> select insert('1234567890',12,3,'ABC');
+---------------------------------+
| insert('1234567890',12,3,'ABC') |
+---------------------------------+
| 1234567890                      |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.01)

测试不同len长度

长度从0开始,也就是替换几个字符。

gbase> select insert('1234567890',2,0,'ABC');
+--------------------------------+
| insert('1234567890',2,0,'ABC') |
+--------------------------------+
| 1ABC234567890                  |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',2,1,'ABC');
+--------------------------------+
| insert('1234567890',2,1,'ABC') |
+--------------------------------+
| 1ABC34567890                   |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',2,2,'ABC');
+--------------------------------+
| insert('1234567890',2,2,'ABC') |
+--------------------------------+
| 1ABC4567890                    |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',2,3,'ABC');
+--------------------------------+
| insert('1234567890',2,3,'ABC') |
+--------------------------------+
| 1ABC567890                     |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',2,4,'ABC');
+--------------------------------+
| insert('1234567890',2,4,'ABC') |
+--------------------------------+
| 1ABC67890                      |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

长度超过末尾时

只替换到末尾,超过部分无效。

gbase> select insert('1234567890',2,8,'ABC');
+--------------------------------+
| insert('1234567890',2,8,'ABC') |
+--------------------------------+
| 1ABC0                          |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',2,9,'ABC');
+--------------------------------+
| insert('1234567890',2,9,'ABC') |
+--------------------------------+
| 1ABC                           |
+--------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',2,10,'ABC');
+---------------------------------+
| insert('1234567890',2,10,'ABC') |
+---------------------------------+
| 1ABC                            |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',2,100,'ABC');
+----------------------------------+
| insert('1234567890',2,100,'ABC') |
+----------------------------------+
| 1ABC                             |
+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

任何参数为NULL

任何参数为NULL,返回都是NULL. 下面例子,原始字符串为NULL就不贴了。

gbase> select insert('1234567890',null,100,'ABC');
+-------------------------------------+
| insert('1234567890',null,100,'ABC') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',2,null,'ABC');
+-----------------------------------+
| insert('1234567890',2,null,'ABC') |
+-----------------------------------+
| NULL                              |
+-----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select insert('1234567890',2,100,null);
+---------------------------------+
| insert('1234567890',2,100,null) |
+---------------------------------+
| NULL                            |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)

regexp_replace

请点击标题连接的另外一个帖子