Hologres兼容PostgreSQL,支持使用标准的PostgreSQL语法进行开发。本文为您介绍Hologres已支持的字符串转换函数和字符串截取函数列表及使用用例。

字符串转换函数

Hologres已支持的字符串转换函数列表如下。当前Hologres版本支持的函数是PostgreSQL的一个子集,函数的使用方法请参见类型转换函数
函数名返回类型描述用例结果
to_number(text, text)NUMERIC转换字符串为数字。
select to_number('12,454.8-', '99G999D9S');
12454.8
string_agg(expression,delimiter)TEXT使用指定分隔符将指定表达式的非空值串联成字符串。可作为列转行使用。
create table city_test(
  country text,
  city text
);
insert into city_test values ('中国' ,'上海'),('中国','台湾'),('日本','东京'),('法国','巴黎'),('英国','伦敦');
select string_agg(city,',') from city_test;
string_agg
------------------------
上海,台湾,东京,巴黎,伦敦
string_agg (expression [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]TEXT使用指定分隔符将指定表达式的非空值串联成字符串。可作为列转行使用,同时在表达式中支持filter过滤条件。
说明 Hologres V1.3及以上版本支持。
create table name_text(student_id int,name text)
insert into name_text values (1,'张三'),(2,'李四'),(3,'王五'),(4,'周六');
select string_agg(name,',') filter (where student_id >2) from name_text ;
string_agg
----------
王五,周六
regexp_split_to_table(string text, pattern text)TEXT字符串分割函数,将分割出的数据转换成行,可作为行转列使用。
create table  interests_test(name text,intrests text);
insert into interests_test values ('张三','唱歌,跳舞'),('李四','踢球,跑步,画画'),('王五','插花,书法,弹琴,睡觉');
select name,regexp_split_to_table(intrests, ',') from interests_test;
name | regexp_split_to_table
---------------------------
张三 | 唱歌
张三 | 跳舞
李四 | 踢球
李四 | 跑步
李四 | 画画
王五 | 插花
王五 | 书法
王五 | 弹琴
王五 | 睡觉
regexp_split_to_array(string text, pattern text)ARRAY字符串分割函数,将分割出的数据转换成数组,可作为行转数组使用。
create table  interests_test(name text,intrests text);
insert into interests_test values ('张三','唱歌,跳舞'),('李四','踢球,跑步,画画'),('王五','插花,书法,弹琴,睡觉');
select name,regexp_split_to_array(intrests, ',') from interests_test;
name | regexp_split_to_array
----------------------------
张三 | {唱歌,跳舞}
李四 | {踢球,跑步,画画}
王五 | {插花,书法,弹琴,睡觉}
replace(string text, old_text, new_text) TEXT将字符串中原有的子字串替换成新的子字串。
create table animal_test(
  animal text,
  color text );
insert into animal_test values ('狗','白色'),('猫','白色'),('老虎','黄色');
select animal,replace(color,'白','五彩斑斓') from animal_test;
animal |  replace   
-------------------
狗     | 五彩斑斓色
猫     | 五彩斑斓色
老虎   | 黄色
regexp_replace(string, regex, replacement[, flags]) TEXT通过POSIX 正则表达式将子字符串替换为新的子字符串。
create table a_test(a text);
insert into a_test values ('Abcd1234abCd');
SELECT regexp_replace(a, '1234.', '77', 'ig') from a_test;
regexp_replace 
--------------
Abcd77bCd
regexp_match(string text, pattern text) ARRAY对字符串按正则表达式进行匹配,如果存在则会在结果数组中表示出来。
select regexp_match('foobarbequebaz', '(bar)(beque)')
regexp_match
------------
{bar,beque}
row_to_jsonJSON将一行数据转换为JSON格式。
说明 Hologres V1.3及以上版本支持。
create table  interests_test(name text,intrests text);
insert into interests_test values ('张三','唱歌,跳舞'),('李四','踢球,跑步,画画'),('王五','插花,书法,弹琴,睡觉');
select row_to_json(t) from (select name,intrests from interests_test) as t;
row_to_json                
------------------------------
{"f1":"张三","f2":"唱歌,跳舞"}
{"f1":"李四","f2":"踢球,跑步,画画"}
{"f1":"王五","f2":"插花,书法,弹琴,睡觉"}
row()ARRAY/TEXT返回公式所在的行号。
说明 Hologres V1.3及以上版本支持。
create table row_test(a text,b text);
insert into row_test values('0','0'),('1','0');
select a,b from row_test where row(a,b)=row(b,a);
a | b 
------
0 | 0

字符串截取函数

Hologres已支持的字符串截取函数列表如下。当前Hologres版本支持的函数是PostgreSQL的一个子集,函数的使用方法请参见字符串截取函数
函数名返回类型描述用例结果
split_part(string text ,delimiter text,n int)TEXT使用指定的分隔符拆分字符串,并返回第n个字串。
create table split_part_test(a text);
insert into split_part_test values ('a/b/c/d/e'),('a1/b1/c1/d1/e1');
select split_part(a, '/', 2) from split_part_test;
split_part 
----------
b
b1
concat(str "any" [, str "any" [, ...] ])TEXT连接所有参数。忽略NULL参数。
select concat('abcde', 2, NULL, 22);
concat
---------
abcde222
concat_ws(sep text, str "any" [, str "any" [, ...] ])TEXT使用分隔符连接除第一个参数外的所有参数。
说明 第一个参数用作分隔符字符串。忽略NULL参数。
select concat_ws(',', 'abcde', 2, NULL, 22);
concat_ws
----------
abcde,2,22
substring(string [from int] [for int])TEXT从字符串中找出指定的子字符串。
select substring('Thomas' from 2 for 3);
substring
----------
hom
substring(string from pattern)TEXT从字符串中找出与POSIX正则表达式匹配的子字符串。
select substring('Thomas' from '...$');
substring
----------
mas
substring(string from pattern for escape)TEXT从字符串中找出与SQL正则表达式匹配的子字符串。
select substring('Thomas' from '%#"o_a#"_' for '#');
substring
----------
oma
left(string text, n int)TEXT返回字符串的前n个字符。n为负数时,返回除最后-n个字符之外的所有字符。
select left('hologres', 4);
left
----
holo
right(string text, n int)TEXT返回字符串的后n个字符。n为负数时,返回除前-n个字符之外的所有字符。
select right('hologres', 4);
right
----------
gres
string || stringTEXT连接两个字符串。
select 'Holo' || 'greSQL';
HologreSQL
bit_length(string) TEXT获取字符串的位长度。
select bit_length('jose');
32
char_length(string)TEXT获取字符串的字符长度。
select char_length('jose');
4
length(string)TEXT获取字符串的字节长度,使用utf8编码字符集时,一个汉字是3字节,一个数字或字母算一个字节。
说明 length函数的计算结果和char_length函数相同,因为英文字符的个数和所占字节相同,一个字符占一个字节。
select length('jose');
4
lower(string)TEXT转换字符串为小写格式。
select lower('TOM');
tom
upper(string)TEXT转换字符串为大写格式。
select upper('tom');
TOM
initcap(string)TEXT将每个单词的第一个字母转换为大写,其余字母转换为小写。
说明 单词是由一系列字母和数字组成的字符,使用非字母或数字分隔。
select initcap('hi THOMAS');
Hi Thomas
octet_length(string)TEXT返回字符串的字节数。
select octet_length('jose');
4
position(substring in string)TEXT查找子字符串在字符串中的位置。
select position('om' in 'Thomas');
3
strpos(string, substring)TEXT查找子字符串在目标字符串中的位置。
select strpos('high', 'ig');
2
trim([leading | trailing | both] [characters] from string)TEXT从字符串String的开始、结尾或两端删除仅包含Characters中字符的最长字符串。
说明
  • 默认从两端删除。
  • 默认仅包含Characters中字符的最长字符串为空格
select trim(both 'xyz' from 'yxTomxx');
Tom
btrim(string text [, characters text])TEXT从字符串String的开始和结尾删除仅包含Characters中字符的最长字符串。
说明 默认仅包含Characters中字符的最长字符串为空格
select btrim('xyxtrimyyx', 'xyz');
trim
ltrim(string text [, characters text])TEXT从字符串String的开始删除只包含Characters 中字符的最长的字符串。
说明 如果没有指定Characters的值,则Characters默认是空格。
select ltrim('zzzytest', 'xyz');
test
rtrim(string text [, characters text])TEXT从字符串String的末尾删除只包含Characters中字符的最长的字符串。
说明 如果没有指定Characters的值,则Characters默认是空格。
select rtrim('testxxzx', 'xyz');
test
lpad(string text, length int [, fill text])TEXT用Fill填充在String头部,将String填充为长度是Length的字符串。
说明
  • 如果String的长度已经超过Length,则从右侧将String截断为长度是Length的字符串。
  • 如果没有指定Fill的值,则Fill默认为空格
select lpad('hi', 5, 'xy');
xyxhi
rpad(string text, length int [, fill text])TEXT对字符串的右边进行填充,填充内容为指定长度的字符串。
select rpad('hi', 5, 'xy');
hixyx
md5(string)TEXT计算String的MD5哈希值。结果表示为十六进制的形式。
select md5('abc');
900150983cd24fb0d6963f7d28e17f72
parse_ident(quali_iden text [,...] )TEXT解析字符串。
select parse_ident('"SomeSchema".someTable');
{SomeSchema,sometable}
quote_ident(string text)TEXT使用String作为合法的SQL标识符。
说明 当字符串包含非标识符字符或者字符串会转换大小写时,需要添加引号。
select quote_ident('Foo bar');
"Foo bar"
quote_literal(string text)TEXT将String转换为合法的SQL语句字符串的常量形式。
select quote_literal(E'O\'Reilly');
'O''Reilly'
ascii(string)TEXT返回参数第一个字符的ASCII码。
select ascii('x');
120
chr(int)TEXT返回指定编码值对应的字符。
说明 参数必须是合法的ASCII或UTF8编码值,并且参数值不能为0
select chr(65);
A
repeat(string text, number int)TEXT将String重复指定Nnumber次。
select repeat('Pg', 4);
PgPgPgPg
starts_with(string, prefix)BOOLEAN如果字符串以前缀开头,则返回true;否则返回false
select starts_with('alphabet', 'alph');
t
to_hex(number int or bigint)TEXT将数字转换为十六进制的表示形式。
select to_hex(2147483647);
7fffffff
translate(string text, from text, to text)TEXT使用字符串To中的字符替换From中的字符。
select translate('12345', '143', 'ax');
a2x5