字符处理函数和操作符

相比于原始的openGauss，dolphin对于字符处理函数和操作符的修改主要为：

  1. 新增regexp/not regexp/rlike操作符。
  2. 新增locate/lcase/ucase/insert/bin/char/elt/field/find_int_set/hex/space/soundex/export_set/ord/substring_index/from_base64函数。
  3. 修改length/bit_length/octet_length/convert/format/left/right函数的表现。
  4. 新增^操作符的异或功能，新增like binary/not like binary操作符。
  5. 修改like/not like 操作符的表现。
  6. 新增!操作符，可在表达式前使用，其效果与NOT一致。

  7. 新增text_bool/varchar_bool/char_bool函数。

  8. bit_length(string)

描述：字符串的位数。对于二进制输入，将向上补齐至8的倍数。

返回值类型：int

示例：

``` 1. openGauss=# SELECT bit_length('world'); 2. bit_length 3. ------------ 4. 40 5. (1 row)

  1. openGauss=# SELECT bit_length(b'010');
  2. bit_length
  4. 8
  5. (1 row) ```
  6. insert(des text, start int, length int, src text)

描述：在原字符串的指定位置插入一个新字符串，并从指定位置开始替换掉原字符串一定数量的字符。

返回值类型：text

示例：

1. openGauss=# select insert('abcdefg', 2, 4, 'yyy'); 2. insert 3. -------- 4. ayyyfg 5. (1 row) - lcase(string)

描述：把字符串转化为小写，等价于lower。

返回值类型：varchar

示例：

1. openGauss=# SELECT lcase('TOM'); 2. lcase 3. ------- 4. tom 5. (1 row) - length(string)

描述：获取参数string中字符的数目。对于多字符编码（如中文），返回字节数。

返回值类型：integer

示例：

``` 1. openGauss=# SELECT length('abcd'); 2. length 3. -------- 4. 4 5. (1 row)

  1. openGauss=# SELECT length('中文');
  2. length
  4. 6
  5. (1 row) ```
  6. format(val number, dec_num int [,locale string])

描述：将val以”x,xxx,xxx.xx”的格式返回。val将保留dec_num位小数。保留的小数位数最多为32位，若dec_num大于32，则以保留32位小数返回。若dec_num为0，则返回内容无小数点及小数部分数字。第三个参数可选，可以根据locale指定返回内容的小数点及千位分隔符的格式。如果没有指定第三个参数，或第三个参数值非法，则使用默认值’en_US’。

注意：此format函数针对B兼容数据库使用，与openGauss原有的format函数语义不同。若想使用此语义，请创建B兼容模式数据库，启用MySQL兼容性SQL引擎插件，并将dolphin.b_compatibility_mode设置为TRUE.

返回值类型：text

示例：

``` 1. openGauss=# CREATE DATABASE B_COMPATIBILITY_DATABASE DBCOMPATIBILITY 'B'; 2. CREATE DATABASE 3. openGauss=# \c B_COMPATIBILITY_DATABASE 4. b_compatibility_database=# CREATE Extension dolphin; 5. CREATE Extension 6. b_compatibility_database=# SET dolphin.b_compatibility_mode = TRUE; 7. SET 8. b_compatibility_database=# select format(1234.4567,2); 9. format 10. ----------- 11. 1,234.46 12. (1 row)

  1. b_compatibility_database=# select format(1234.5,4);
  2. format
  4. 1,234.5000

  5. (1 row)

  6. b_compatibility_database=# select format(1234.5,0);

  7. format
  9. 1,235

  10. (1 row)

  11. b_compatibility_database=# select format(1234.5,2,'de_DE');

  12. format
  14. 1.234,50
  15. (1 row) ```
  16. hex(number or string or bytea or bit)

描述：把数字、字符、二进制字符类型或位串类型转换成十六进制表现形式

注意：openGauss将反斜杠’’单独看做一个字符，因此对于字符串’\n’，其长度为2。

返回值类型：text

示例：

``` 1. openGauss=# SELECT hex(256); 2. hex 3. ----- 4. 100 5. (1 row)

  1. openGauss=# select hex('abc');
  2. hex
  4. 616263

  5. (1 row)

  6. openGauss=# select hex('abc'::bytea);

  7. hex
  9. 616263

  10. (1 row)

  11. openGauss=# select hex(b'1111');

  12. hex
  14. 0f

  15. (1 row)

  16. openGauss=# select hex('\n');

  17. hex
  19. 5c6e
  20. (1 row) ```
  21. locate(substring, string [,position])

描述：从字符串string的position（缺省时为1）所指的位置开始查找并返回第1次出现子串substring的位置的值。字符串区分大小写。

  • 当position为0时，返回0。
  • 当position为负数时，从字符串倒数第n个字符往前逆向搜索。n为position的绝对值。

返回值类型：integer，字符串不存在时返回0。

示例：

``` 1. openGauss=# SELECT locate('ing', 'string'); 2. locate 3. -------- 4. 4 5. (1 row)

  1. openGauss=# SELECT locate('ing', 'string', 0);
  2. locate
  4. 0

  5. (1 row)

  6. openGauss=# SELECT locate('ing', 'string', 5);

  7. locate
  9. 0
  10. (1 row) ```
  11. octet_length(string)

描述：等价于length。

返回值类型：int

示例：

1. openGauss=# SELECT octet_length('中文'); 2. octet_length 3. -------------- 4. 6 5. (1 row) - source_string regexp pattern

描述：正则表达式的模式匹配操作符。

source_string为源字符串，pattern为正则表达式匹配模式。

返回值类型：integer（0或1）

示例：

1. openGauss=# SELECT 'str' regexp '[ac]' AS RESULT; 2. result 3. -------- 4. 0 5. (1 row) - source_string not regexp pattern

描述：regexp的结果取反。

source_string为源字符串，pattern为正则表达式匹配模式。

返回值类型：integer（0或1）

示例：

1. openGauss=# SELECT 'str' not regexp '[ac]' AS RESULT; 2. result 3. -------- 4. 1 5. (1 row) - source_string rlike pattern

描述：等价于regexp。

source_string为源字符串，pattern为正则表达式匹配模式。

返回值类型：integer（0或1）

示例：

1. openGauss=# SELECT 'str' rlike '[ac]' AS RESULT; 2. result 3. -------- 4. 0 5. (1 row) - ucase(string)

描述：把字符串转化为大写。等价于upper。

返回值类型：varchar

示例：

1. openGauss=# SELECT ucase('tom'); 2. ucase 3. ------- 4. TOM 5. (1 row) - bin(number or string)

描述：返回N整型或者数字字符的二进制字符串，汉字返回0。

返回值类型：text

示例：

``` 1. b_compatibility_database=# SELECT bin('309'); 2. bin 3. ------------ 4. 100110101 5. (1 row)

  1. b_compatibility_database=# SELECT bin('你好');
  2. bin
  4. 0
  5. (1 row) ```
  6. char(any)

描述：根据ASCII码对多个数字转换为多个字符。

返回值类型：text

示例：

1. b_compatibility_database=# select char(77,77.3,'77.3','78.8',78.8); 2. char 3. ------- 4. MMMNO 5. (1 row) - char_length(string)或character_leng(string)

描述：字符串中的字符个数,一个汉字长度为1，并且支持二进制类型。

返回值类型：int

示例：

1. openGauss=# SELECT char_length('hello'); 2. char_length 3. ------------- 4. 5 5. (1 row) 6. b_compatibility_database=# SELECT char_length(B'101'); 7. char_length 8. ------------- 9. 1 10. (1 row) - convert(expr using transcoding_name)

描述：通过transcoding_name指定的编码方式转换expr; 注意：默认库中支持如下格式： convert(string bytea, src_encoding name, dest_encoding name);以dest_encoding指定的编码方式转换bytea，dolphin下支持通过using关键字后transcoding_name指定要转换的编码方式，对expr进行转换，不支持上述三个参数的表示方式。

返回值类型：text

示例：

``` 1. b_compatibility_database=# select convert('a' using 'utf8'); 2. convert 3. --------- 4. a 5. (1 row)

  1. b_compatibility_database=# select convert('a' using utf8);
  2. convert
  4. a
  5. (1 row) ```
  6. elt(number, str1,str2,str3,…)

描述：返回后面字符串的第N个字符串。

返回值类型：text

示例：

1. b_compatibility_database=# select elt(3,'wo','ceshi','disange'); 2. elt 3. --------- 4. disange 5. (1 row) - left(str text, n int)

描述：返回字符串的前n个字符。当n是负数时，当做0处理。

返回值类型：text

示例：

``` 1. test_db=# select left('abcde', 2); 2. left 3. ------ 4. ab 5. (1 row)

  1. test_db=# select left('abcde', 0);
  2. left

  4. (1 row)

  5. test_db=# select left('abcde', -2);

  6. left

  8. (1 row) ```

  9. right(str text, n int)

描述：返回字符串中的后n个字符。当n是负值时，当做0处理。

返回值类型：text

示例：

``` 1. test_db=# select right('abcde', 2); 2. right 3. ------- 4. de 5. (1 row)

  1. test_db=# select right('abcde', 0);
  2. right

  4. (1 row)

  5. test_db=# select right('abcde', -2);

  6. right

  8. (1 row) ```

  9. field(str, str1,str2,str3,…)

描述：获取str在后面strn中的位置，不区分大小写。

返回值类型：int

示例：

1. b_compatibility_database=# select field('ceshi','wo','ceshi','disange'); 2. field 3. ------- 4. 2 5. (1 row) - find_in_set(str, strlist)

描述：获取str在后面strlist中的位置，不区分大小写,strlist以，分割。

返回值类型：int

示例：

1. b_compatibility_database=# select find_in_set('ceshi','wo','ceshi,ni,wo,ta'); 2. find_in_set 3. ------------- 4. 3 5. (1 row) - space(number)

描述：返回N个空格

返回值类型：text

示例：

``` 1. b_compatibility_database=# select space('5'); 2. space 3. -------

  1. (1 row) ```
  2. soundex(str)

描述：返回描述指定字符串的语音表示的字母数字模式的算法

返回值类型：text

示例：

1. b_compatibility_database=# select soundex('abcqwcaa'); 2. soundex 3. --------- 4. A120 5. (1 row) - make_set(number, string1, string2, …)

描述：返回一个由number中设置了相应位的字符串组成的设置值（包含子字符串的字符串，以,分隔）。string1对应位0，string2对应位1，依此类推。 string1，string2，…中的NULL值不添加到结果中。

返回值类型：text

1. select make_set(1|4, 'one', 'two', NULL, 'four'); 2. make_set 3. ---------- 4. one 5. (1 row) - ^

描述：实现两个字符串的异或功能，截取第一个非数值型符号前的内容作异或。

返回值类型：INT

示例：

1. openGauss=# SELECT '123a'^'123'; 2. ?column? 3. --------- 4. 0 5. (1 row) - like/not like

描述：判断字符串能否匹配上LIKE后的模式字符串。opengauss的原like为大小写敏感匹配，现将其改为当dolphin.b_compatibility_mode为TRUE时大小写不敏感匹配，当dolphin.b_compatibility_mode为FALSE时大小写敏感匹配。若字符串与提供的模式匹配，则like表达式返回真(ilike返回假)。

注意事项：

  • 在dolphin插件中增加了该操作符对true/false的bool类型兼容；
  • 对于定长字符串char，若插入表的字符串长度小于指定长度则会在字符串末尾自动填充空格，在dolphin插件中，该操作符处理定长字符串类型时忽略末尾多余空格。

返回值类型：布尔型

示例：

``` 1. openGauss=# SELECT 'a' like 'A' as result; 2. result 3. ------------ 4. t 5. (1 row)

  1. openGauss=# SELECT 'abc' like 'a' as result;
  2. result
  4. f

  5. (1 row)

  6. openGauss=# SELECT 'abc' like 'A%' as result;

  7. result
  9. t

  10. (1 row)

  11. openGauss=# SELECT true like true as result;

  12. result
  14. t
  15. (1 row) ```
  16. like binary/not like binary

描述：判断字符串能否匹配上LIKE BINARY后的模式字符串,like binary采用大小写敏感模式匹配，若模式匹配则返回真(not like binary返回假)，不匹配则返回假(not like binary返回真)。

返回值类型：布尔型

示例：

``` 1. openGauss=# SELECT 'a' like binary 'A' as result; 2. result 3. ------------ 4. f 5. (1 row)

  1. openGauss=# SELECT 'a' like binary 'a' as result;
  2. result
  4. t

  5. (1 row)

  6. openGauss=# SELECT 'abc' like binary 'a' as result;

  7. result
  9. f

  10. (1 row)

  11. openGauss=# SELECT 'abc' like binary 'a%' as result;

  12. result
  14. t
  15. (1 row) ```
  16. substring_index(str, delim, count)

描述：substring_index(str, delim, count)返回str的开始位置至匹配到第count次delim的位置之间的子字符串，count表示匹配的次数。若count为正数，则从str的左边开始匹配，并返回匹配位置左边的子字符串；若count为负数，则从str的右边开始匹配，并返回匹配位置右边的子字符串。count取值范围为INT64_MIN～INT64_MAX。

返回值类型：text

示例：

1. openGauss=# SELECT instr('abcdabcdabcd', 'bcd', 2); 2. substring_index 3. ----------------- 4. abcda 5. (1 row) - export_set(bits, on, off, separator, number of bits)

描述：返回一个字符串，该字符串将显示位数。该函数需要5个自变量才能起作用。该函数将第一个参数(即整数)转换为二进制数字，如果二进制数字为1，则返回“on”，如果二进制数字为0，则返回“off”。

返回值类型：text

示例：

1. openGauss=# SELECT EXPORT_SET(5,'Y','N',',',5); 2. export_set 3. ------------- 4. Y,N,Y,N,N 5. (1 row) - FROM_BASE64

描述:根据BASE64编码规则，将一个BASE64编码的字符串解码，返回字符串的解码结果。

返回值类型：text

编码规则：

  • 将输入的每三个字节（24位）变成四个字节（32位）：6位为一组，高位补两个0，组成一个字节，这样正好能将三个字节补成四个字节，其中每个字节只会对应0(00000000)到63(00111111)。
  • 每76个字符加一个换行符。
  • 编码0(00000000)到61(00111111)对应A-Z，a-z，0-9共62个字符，62(00111110)的编码是’+’，63(00111111)的编码是’/‘。
  • 若输入的字符串字节数不为三的倍数，那么剩余的字节根据编码规则转换，若有一个字节不满8位，则在低位补0补满8位，同时用’=’将转换结果补满四个字节。若最后一组只有两个字节，每6位一组，第三组只有4位，低位要补两个0，然后这三组再分别高位补两个0，转成三个字符，末尾补一个’=’；若最后一组只有一个字节，每6位一组，第二组只有2位，低位要补四个0，然后这两组再分别高位补两个0，转成两个字符，末尾补两个’=’。

解码规则：

  • 将输入的字符串用二进制表示，去掉每个字节高位的两个0。
  • 根据编码规则，正确的编码字节数必为4的倍数。若末尾有’=’，则根据’=’数量去掉最后一个除’=’以外的字节低位的0。若末尾有一个’=’，即最后四个字节为’***=’，则将前三个字节转二进制后再去掉最后两个0，若末尾有两个’=’，即最后四个字节为’**==’，则将前两个字节转二进制后再去掉最后四个0。
  • 将去掉高位0后的各个字节按顺序拼接，每8位转成一个字符。

例子1：YWJj

  1. 字符串用二进制表示为：00011000(Y)00010110(W)00001001(J)00100011(j)。
  2. 去掉每个字节高位的两个0后变成：011000 010110 001001 100011。
  3. 将去掉高位0后的各个字节按顺序拼接成：01100001(a)01100010(b)01100011(c)。
  4. 故解码结果为abc。

例子2：YWI=

  1. 字符串用二进制表示为：00011000(Y)00010110(W)00001000(I)。
  2. 去掉每个字节高位的两个0后变成：011000 010110 001000。
  3. 由于末尾有一个’=’，则第三个字节末尾的0也要去掉再拼接：01100001 01100010。
  4. 故解码结果为ab。

示例：

1. openGauss=# SELECT FROM_BASE64('YWJj'); 2. from_base64 3. ------------- 4. abc 5. (1 row) - ORD(str)。

描述: 返回str的最左边的字符的数值，并使用下面公式计算该字符组成字节的对应数值：

1. (1st byte code) 2. + (2nd byte code 256) 3. + (3rd byte code 256^2) ...

返回值类型：INT

示例:

``` 1. -- test 1 byte 2. openGauss=# select ord('1111'); 3. ord 4. ----- 5. 49 6. (1 row)

  1. openGauss=# select ord('sss111');
  2. ord
  4. 115

  5. (1 row)

  6. -- test 2 byte

  7. openGauss=# select ord('Ŷ1111');
  8. ord
  10. 50614

  11. (1 row)

  12. openGauss=# select ord('߷1111');

  13. ord
  15. 57271

  16. (1 row)

  17. -- test 3 byte

  18. openGauss=# select ord('অ1111');
  19. ord
  21. 14722693

  22. (1 row)

  23. openGauss=# select ord('ꬤ1111');

  24. ord
  26. 15379620

  27. (1 row)

  28. -- test 4 byte

  29. openGauss=# select ord('𒁖1111');
  30. ord
  32. 4036133270

  33. (1 row)

  34. openGauss=# select ord('𓃔1111');

  35. ord
  37. 4036199316

  38. (1 row) ```

  39. TO_BASE64(str)

描述：根据BASE64编码规则，将一个字符串编码成BASE64编码格式，返回字符串的编码结果。编码规则与解码规则和FROM_BASE64相同。

返回值类型：text

注意事项

  • 如果输入的是NULL，那么返回的结果为NULL。
  • 编码解码规则与函数FROM_BASE64相同。

例子1：abc

  1. 将字符串用二进制表示：01100001(a)01100010(b)01100011(c)
  2. 将二进制串拆分，每6位一组：011000 010110 001001 100011
  3. 在每一组的高位都补上两个0：00011000 00010110 00001001 00100011
  4. 查找base64编码转换表：00011000，00010110，00001001，00100011对应的字符为：Y,W,J,j
  5. 故编码结果为YWJj

例子2：ab

  1. 将字符串用二进制表示：01100001(a)01100010(b)
  2. 将二进制串拆分，每6位一组，由于最后一组只有4位，在低位补0补够6位：011000 010110 0010(00)
  3. 在每一组的高位都补上两个0：00011000 00010110 00001000
  4. 查找base64编码转换表：00011000，00010110，00001000对应的字符为：Y,W,I
  5. 由于输入的字符串字节数不为三的倍数，导致转换后的字符数不为4的倍数，所以最后需要用=号补满4个字节，最终编码结果为：YWI=

示例：

``` 1. SELECT TO_BASE64('to_base64'); 2. to_base64 3. -------------- 4. dG9fYmFzZTY0 5. (1 row) 6. SELECT TO_BASE64('123456'); 7. to_base64 8. ----------- 9. MTIzNDU2 10. (1 row)

  1. SELECT TO_BASE64('12345');
  2. to_base64
  4. MTIzNDU=

  5. (1 row)

  6. SELECT TO_BASE64('1234');

  7. to_base64
  9. MTIzNA==
  10. (1 row) ```
  11. UNHEX(str)

描述：将一个十六进制编码的字符串解码，一个十六进制字符变成4位二进制，两个十六进制字符（8位）解码为一个字符，返回字符串的解码结果。若十六进制字符串的字符数不为偶数，则在高位补0。若输入的是二进制格式的字符串，则返回NULL。

返回值类型：text

注意事项

  • 如果输入的是NULL或者包含非十六进制字符，那么返回的结果为NULL。
  • 若输入的是数字，则将数字转成字符串后进行解码，如需将十六进制数转为十进制数，则需要使用其它的函数
  • 编码解码规则与函数HEX相同。

例子1：4142

  1. 将每个十六进制字符用4位二进制表示，若十六进制字符数不为偶数，则在高位补0：0100(4)0001(1)0100(4)0010(2)
  2. 每8位组成一个字符：01000001 01000010
  3. 故解码结果为AB

示例：

``` 1. SELECT UNHEX('6f70656e4761757373'); 2. unhex 3. ----------- 4. openGauss 5. (1 row)

  1. SELECT UNHEX(HEX('string'));
  2. unhex
  4. string

  5. (1 row)

  6. SELECT HEX(UNHEX('1267'));

  7. hex
  9. 1267
  10. (1 row) ```
  11. !

描述：在表达式前使用，实现逻辑运算“非”。其效果与表达式前使用NOT一致。

返回值类型：boolean

注意事项

  • 该运算符仅在b_compatibility_mode为TRUE时可用。
  • openGauss原有阶乘运算符”!!”。为避免与本运算符混淆，当b_compatibility_mode为TRUE时，阶乘运算符”!!“与”!“不可使用。请使用函数factorial替代。
  • 当b_compatibility_mode为TRUE时，不允许多个”!“运算符连用。
  • 仅有可转换为boolean类型的表达式可使用本操作符。详情查看系统表pg_cast。

示例：

``` 1. openGauss=# set b_compatibility_mode = 1; 2. SET 3. openGauss=# select !10; 4. ?column? 5. ---------- 6. f 7. (1 row)

  1. openGauss=# select !true;
  2. ?column?
  4. f

  5. (1 row)

  6. openGauss=# select !!10;

  7. ERROR: Operator '!!' is deprecated when b_compatibility_mode is on. Please use function factorial().

  8. openGauss=# select 10!;

  9. ERROR: Operator '!' behind expression is deprecated when b_compatibility_mode is on. Please use function factorial(). ```
  10. text_bool(text)

描述：先截取输入文本首部的数值部分（包括整数、小数、正负数），丢弃剩下的非数值部分。若截取得到的数值部分等于0，则函数返回逻辑假；否则函数返回逻辑真。若输入文本首部不是数值，则直接返回逻辑假。

返回类型：boolean

示例：

1. openGauss=# select text_bool('-0.01abc'); 2. text_bool 3. ----------- 4. t 5. (1 row)

1. openGauss=# select text_bool('0abc'); 2. text_bool 3. ----------- 4. f 5. (1 row)

1. openGauss=# select text_bool('abc'); 2. text_bool 3. ----------- 4. f 5. (1 row) - varchar_bool(varchar)

描述：先截取输入的变长字符串首部的数值部分（包括整数、小数、正负数），丢弃剩下的非数值部分。若截取得到的数值部分等于0，则函数返回逻辑假；否则函数返回逻辑真。若输入的变长字符串首部不是数值，则直接返回逻辑假。

返回类型：boolean

示例：

1. openGauss=# select varchar_bool('-0.0100abc'); 2. varchar_bool 3. -------------- 4. t 5. (1 row)

1. openGauss=# select varchar_bool('0abc'); 2. varchar_bool 3. -------------- 4. f 5. (1 row)

1. openGauss=# select varchar_bool('abc'); 2. varchar_bool 3. -------------- 4. f 5. (1 row) - char_bool(char)

描述：先截取输入字符串首部的数值部分（包括整数、小数、正负数），丢弃剩下的非数值部分。若截取得到的数值部分等于0，则函数返回逻辑假；否则函数返回逻辑真。若输入字符串首部不是数值，则直接返回逻辑假。

返回类型：boolean

示例：

1. openGauss=# select char_bool('-0.0100abc'); 2. char_bool 3. ----------- 4. t 5. (1 row)

1. openGauss=# select char_bool('0abc'); 2. char_bool 3. ----------- 4. f 5. (1 row)

1. openGauss=# select char_bool('abc'); 2. char_bool 3. ----------- 4. f 5. (1 row)