文档

字符串函数

更新时间:

当您需要转换或截取Hologres中存储的字符串时,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_json

    JSON

    将一行数据转换为JSON格式。

    说明
    • Hologres V1.3及以上版本支持。

    • Hologres从 V1.3.52版本开始,JSON中的Key支持根据列名生成。

    • 最多支持50列。

    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;
    • V1.3.52以下版本返回结果:

      row_to_json                
      ------------------------------
      {"f1":"张三","f2":"唱歌,跳舞"}
      {"f1":"李四","f2":"踢球,跑步,画画"}
      {"f1":"王五","f2":"插花,书法,弹琴,睡觉"}
    • V1.3.52及以上版本返回结果示例:

      row_to_json                
      ------------------------------
      "{"name" : "王五", "intrests" : "插花,书法,弹琴,睡觉"}"
      "{"name" : "张三", "intrests" : "唱歌,跳舞"}"
      "{"name" : "李四", "intrests" : "踢球,跑步,画画"}"

    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 || string

    TEXT

    连接两个字符串。

    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重复指定number次。

    select repeat('Pg', 4);

    PgPgPgPg

    starts_with(string, prefix)

    BOOLEAN

    如果字符串以前缀开头,则返回ttrue);否则返回f(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

    • 本页导读 (1)
    文档反馈