db2 locate函数、position函数、posstr函数、字符串匹配函数

以下讲解一下db2中的字符串匹配函数。

一、locate函数

1.1、语法

语法：LOCATE(str_expr1,str_expr2[,start])

示例：查询user表中用户姓名包含王的数据
select * from user where locate('王',user_name) >0

返回str_expr1第一次在str_expr2中出现的位置，若给出第三个参数则表明从str_expr2的第start位开始搜索

注意：
1.位置下标默认从1开始，若未找到则返回0
2.locate中的两个参数，若任意一个参数为null，则返回null
3.若查找字符中包含中文，则返回位置可能不是真实位置(返回字节位置)，需要加codeunits16处理

codeunits16相关知识参考：
https://www.ibm.com/docs/zh/db2/10.5?topic=sce-db2-cancun-release-string-unit-attributes-improve-handling-multibyte-characters

1.2、示例

1.2.1、查找英文字符

db2 => values LOCATE('c','abcd');

1
-----------
3

1.2.2、查找中文字符（注意有坑）

注意：若查询内容中包含了中文字符，需要在后面添加codeunits16，否则虽然能返回位置，但是返回的位置不对。

db2 => values LOCATE('中','我爱中国',codeunits16);

1
-----------
3

我们看一下上面这个语句若不加codeunits16会怎么样？

若不加codeunits16则默认返回的是字节的位置，可以通过length函数计算字节长度

db2 => values length('我爱',octets);

1
-----------
4   -- 可以发现我爱这两个字占了4个字节，所以中的位置是5

1.2.3、从指定位置查找

db2 => values LOCATE('中','我爱中国',2,codeunits16);

1
-----------
3

db2 => values LOCATE('中','我爱中国',4,codeunits16);

1
-----------
0  -- 从第4位开始查找的时候会返回0，因为查不到

1.2.4、locate参数中包含null （注意有坑）

我们用locate函数是希望查找指定内容，若查找不到则返回0的，但是需要注意的是也可能会返回null的情况

db2 => values LOCATE(null,'我爱中国',codeunits16);

1
-----------
null -- 注意：locate的前两个查询内容，若任意一个为null, 则返回null


上面我们测试了包含null的情况，那如果是空字符串''会怎么样呢？
db2 => values LOCATE('','我爱中国',codeunits16);

1
-----------
1 -- 注意：当查找内容是空字符串''的时候返回了1，这里也可能会导致踩坑，请注意

二、position函数

2.1、语法

语法：position(expr1,expr2)

返回expr1在expr2中第一次出现的起始位置，如果找不到子字符串，‌则返回0。
经测试，代码单元用octets也可以得到正确结果。

注意：
1.若第一个参数expr1为null则抛异常；若第二个参数expr2为null则返回null; 若第一个参数expr1为空字符串''则返回1

2.2、示例

2.2.1、查询包含中文

db2 => values position('中','我爱中国',codeunits16);

1
-----------
3

2.2.2、查询空内容（注意有坑）

这里我们测试一下当查询内容有null、空字符串’'的时候会怎么样？

db2 => values position(null,'我爱中国',codeunits16);

1
-----------
抛异常 -- 注意：当第一个参数查询内容为null时，数据库抛异常


db2 => values position('我',null,codeunits16);

1
-----------
null -- 注意：当第二个参数为null时，返回null

上面我们测试了包含null的情况，那如果是空字符串''会怎么样呢？
db2 => values position('','我爱中国',codeunits16);

1
-----------
1 -- 注意：当查找内容是空字符串''的时候返回了1，这里也可能会导致踩坑，请注意

三、posstr函数

3.1、语法

语法：posstr(source_str,search_str)

返回一个字符串(search_str)在另一个字符串(source_str)中第一次出现的起始位置。默认返回的是字节所在的位置。

注意：
1.posstr返回的是字节所在的位置，不支持返回真实字符所占位置

3.2、示例

3.2.1、查询字符所在位置

注意：posstr返回的是字节所在的位置，不支持返回真实字符所占位置

db2 => values posstr('我爱中国','中');

1
-----------
5

3.2.2、查询空内容(注意有坑)

注意：若有任意一个参数为null, 则返回null；若搜索内容为空字符串’'则返回1

db2 => values posstr('我爱中国',null);

1
-----------
null  -- 若有任意一个参数为null, 则返回null

db2 => values  posstr(null,'中') ;

1
-----------
null

db2 => values posstr('我爱中国','');

1
-----------
1 -- 当搜索内容是空字符串''时，返回1

四、locate、position、posstr的区别

主要区别如下：

1. locate允许指定搜索的起始位置。‌其他两个不支持
2. 三者对于null或空字符串的处理逻辑不太一样
3. locate、position支持查询中文字符的位置，posstr只支持返回字节的位置