实时数仓Hologres：字符串函数

描述：连接两个字符串。

<string_value1> TEXT || <string_value2> TEXT

参数说明

string_value1和string_value2：想要连接的字符串表达式。

返回值说明

返回TEXT类型。

示例

--返回结果：HologreSQL
SELECT 'Holo' || 'greSQL';

描述：连接两个或更多字符串值并返回一个连续字符串。

CONCAT(<str1> TEXT, <str2> TEXT, ..., <strN> TEXT)

参数说明

str1,...,strN：这些参数代表想要连接的字符串。

返回值说明

返回TEXT类型。

示例

SELEC CONCAT('abcde', 2, NULL, 22);

返回结果如下。

concat
---------
abcde222

描述：使用分隔符连接除第一个参数外的所有参数。

CONCAT_WS(<sep> TEXT, str "any" [, str "any" [, ...] ])

参数说明

• sep：必填，分隔符，插入到要连接的每个字符串之间。

  说明

  若取值为NULL，该函数将返回NULL。

• str：必填，要连接的字符串。

  说明

  若参数中存在NULL，将被忽略。

返回值说明

返回TEXT类型。

示例

SELECT CONCAT_WS(',', 'abcde', 2, NULL, 22);

返回结果如下。

concat_ws
----------
abcde,2,22

描述：将多行的同一列聚合为一个字符串。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。

LISTAGG(<str1> TEXT [, <str2> TEXT])

参数说明

• str1：必填，要聚合的字符串表达式或列名。

• str2：可选，分割符。

返回值说明

返回TEXT类型。

示例

• 示例1

  --返回结果：hologres
  SELECT LISTAGG(t) FROM (VALUES('holo'), ('gres')) AS l(t);

• 示例2

  --返回结果：holo.gres
  SELECT LISTAGG(t, '.') FROM (VALUES('holo'), ('gres')) AS l(t);

描述：按照指定规则从目标字符串中获取子字符串。

• 从字符串中找出指定位置的子字符串。

  SUBSTRING(<str> TEXT [FROM <num_start> INT] [FOR <num_end> INT])

• 从字符串中找出与POSIX正则表达式匹配的子字符串。

  SUBSTRING(<str> TEXT FROM <pattern> TEXT)

• 从字符串中找出与SQL正则表达式匹配的子字符串。

  SUBSTRING(<str> TEXT FROM <pattern> TEXT FOR <escape> TEXT )

参数说明

• str：必填，目标字符串。

• num_start和num_end：可选，起始和结束位置。

• pattern：必填，正则表达式，用于定义如何匹配和提取字符串中的子串。

• escape：必填，转义字符。

  说明

  在正则表达式中，某些字符如.、*、+等具有特殊意义。如果需要在文本中匹配这些字符本身，而不是它们的特殊含义，就需要在它们前面加上转义字符。

返回值说明

返回TEXT类型。

示例

• 示例1：从字符串中找出指定位置的子字符串。

  SELECT SUBSTRING('Thomas' FROM 2 FOR 3);

  返回结果如下。

  substring
  ----------
  hom

• 示例2：从字符串中找出与POSIX正则表达式匹配的子字符串。

  SELECT SUBSTRING('Thomas' FROM '...$');

  返回结果如下。

  substring
  ----------
  mas

• 示例3：从字符串中找出与SQL正则表达式匹配的子字符串。

  SELECT SUBSTRING('Thomas' FROM '%#"o_a#"_' FOR '#');

  返回结果如下。

  substring
  ----------
  oma

描述：对字符串按正则表达式进行匹配，如果存在则会在结果数组中表示出来。

REGEXP_MATCH(<str> TEXT, <pattern> TEXT) 

参数说明

• str：必填，待匹配的字符串。

• pattern：必填，正则表达式。

返回值说明

返回ARRAY类型。

示例

SELECT regexp_match('foobarbequebaz', '(bar)(beque)');

返回结果如下。

regexp_match
------------
{bar,beque}

描述

• 支持获取字符串中指定位置开始到字符串结束的子字符串。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。

  SUBSTR(<str> TEXT, <num_start> INTEGER)

  参数说明：

  • str：必填，目标字符串。

  • num_start：指定开始获取子字符串的位置。

• 支持从字符串的指定位置开始，获取指定长度的子字符串。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。

  SUBSTR(<str> TEXT, <num_start> INTEGER, <len> INTEGER)

  参数说明：

  • str：必填，目标字符串。

  • num_start：指定开始获取子字符串的位置。

  • len：获取子字符串的长度。

返回值说明

返回TEXT类型。

示例

• 示例1：获取从第4位开始到字符串结束的字符串。

  --返回结果：ogres
  SELECT SUBSTR('Hologres', 4);

• 示例2：从第2位开始获取5位字符。

  --返回结果：ologr
  SELECT SUBSTR('Hologres', 2, 5);

描述：将字符串按照指定的分隔符进行分割，并返回分割后的第num段。

SPLIT_PART(<str> TEXT, <delimiter> TEXT, <num> INT)

参数说明

• str：必填，待拆分的目标字符串。

• delimiter：必填，用来进行分割的分隔符。

• num：必填，整数值，分割后中获取的部分，num的索引从1开始。

返回值说明

返回TEXT类型，若num超出了实际分割出的部分数量，将返回NULL。

示例

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

描述：将多行的同一列聚合为一个字符串。

STRING_AGG(expression [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

使用说明：仅Hologres V1.3及以上版本支持Filter过滤条件。

参数说明

• expression：必填，待合并成字符串的表达式或列名。

• order_by_clause：可选，在合并字符串之前，如何对expression中的值进行排序。如果不指定，数据库可能会按照认为合适的方式排序。

• FILTER ( WHERE filter_clause )：可选，指定一个条件来过滤参与聚合的行。

返回值说明

返回TEXT类型。

示例

• 示例1

  CREATE TABLE city_test (
      country text,
      city text
  );
  
  INSERT INTO city_test
      VALUES ('中国', '上海'), ('中国', '台湾'), ('日本', '东京'), ('法国', '巴黎'), ('英国', '伦敦');
  
  SELECT
      STRING_AGG(city, ',')
  FROM
      city_test;

  返回结果如下。

  string_agg
  ------------------------
  上海,台湾,东京,巴黎,伦敦

• 示例2

  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
  ----------
  王五,周六

描述：从字符串的起始位置开始，提取指定数量的字符。

LEFT(<str> TEXT, <num> INT)

参数说明

• str：必填，目标字符串。

• num：必填，整数值，表示从字符串的起始位置提取的字符数量。

  num为负数时，返回除最后-num个字符之外的所有字符。

返回值说明

返回TEXT类型。

示例

SELECT LEFT('hologres', 4);

返回结果如下。

left
----
holo

描述：从字符串的末尾位置开始，提取指定数量的字符。

RIGHT(<str> TEXT, <num> INT)

参数说明

• str：必填，目标字符串。

• num：必填，整数值，表示从字符串的末尾位置提取的字符数量。

  num为负数时，返回除前-num个字符之外的所有字符。

返回值说明

返回TEXT类型。

示例

SELECT RIGHT('hologres', 4);

返回结果如下。

right
----------
gres

描述：移除字符串首部、尾部或者两端的特定字符。

TRIM([leading | trailing | both] [<characters> TEXT] FROM STRING)

参数说明

• 移除位置，默认从两端移除，参数说明如下：

  • leading：可选，仅从字符串的首部（左侧）移除指定的字符。

  • trailing：可选，从字符串的尾部（右侧）移除指定的字符。

  • both：可选，从字符串的两端移除指定的字符。

• characters：可选，要被移除的特定字符集。

• string：必填，待移除的目标字符串。

返回值说明

返回TEXT类型。

示例

--返回结果：Tom
select TRIM(BOTH 'xyz' FROM 'yxTomxx');

描述：去除字符串两端的指定字符。

BTRIM(<str> TEXT [, <characters> TEXT])

参数说明

• str：必填，目标字符串。

• characters：可选，要在目标字符串两端移除的字符集。

  说明

  该参数是将字符集中的每个字符独立进行匹配，并不是整体匹配。

  如果没有指定characters参数，characters参数默认为空格。

返回值说明

返回TEXT类型。

示例

--返回结果：trim
SELECT BTRIM('xyxtrimyyx', 'xyz');

描述：用于移除字符串左侧（开头）的特定字符。

LTRIM(<str> TEXT [, <characters> TEXT])

参数说明

• str：必填，目标字符串。

• characters：可选，需要从目标字符串左侧（开头）移除的字符集。

  说明

  • 该参数是将字符集中的每个字符独立进行匹配，并不是整体匹配。

  • 如果没有指定characters参数，characters参数默认为空格。

返回值说明

返回TEXT类型。

示例

-- 返回结果：test
SELECT LTRIM('zzzytest', 'xyz');

描述：用于移除字符串右侧（结尾）的特定字符。

RTRIM(<str> TEXT [, <characters> TEXT])

参数说明

• str：必填，目标字符串。

• characters：可选，需要从目标字符串右侧（结尾）移除的字符集，如果没有指定characters参数，characters参数值默认为空格。

  说明

  该参数是将字符集中的每个字符独立进行匹配，并不是整体匹配。

返回值说明

返回TEXT类型。

示例

-- 返回结果：test
SELECT RTRIM('testxxzx', 'xyz');

描述：在字符串的左侧填充指定的字符，直到达到指定的总长度。

LPAD(<str> TEXT, <length> INT [, <fill> TEXT])

参数说明

• str：必填，目标字符串。

• length：必填，结果字符串的总长度。

  说明

  如果目标字符串的长度已经超过length，则从右侧将目标字符串截断为长度为length的字符串。

• fill：可选，用于填充的字符，如果没有指定fill的值，则默认为空格。

返回值说明

返回TEXT类型。

示例

--返回结果：xyxhi
SELECT LPAD('hi', 5, 'xy');

描述：在字符串的右侧填充指定的字符，直到达到指定的总长度。

RPAD(<str> TEXT, <length> INT [, <fill> TEXT])

参数说明

• str：必填，目标字符串。

• length：必填，结果字符串的总长度。

  说明

  如果目标字符串的长度已经超过length，则从右侧将目标字符串截断为长度为length的字符串。

• fill：可选，用于填充的字符，如果没有指定fill的值，则默认为空格。

返回值说明

返回TEXT类型。

示例

--返回结果 hixyx
SELECT RPAD('hi', 5, 'xy');

描述：支持将字符串类型数据转换为数值类型。

TO_NUMBER(<str1> TEXT, <str2> TEXT)

参数说明

• str1：待转换的文本字符串。

• str2：格式模型（format model），用于指定如何解析第一个参数中的文本以转换为数值。

返回值说明

返回NUMERIC类型。

示例

--返回结果:-12454.8
SELECT TO_NUMBER('12,454.8-', '99G999D9S');

描述：支持将在源字符串中查找指定的子字符串，并将其替换为另一个指定的字符串。

REPLACE(<str> TEXT, <old_str> TEXT, <new_str> TEXT) 

参数说明

• str：必填，目标的字符串。

• old_str：必填，目标字符串需要被替换的部分。

• new_str：必填，被替换的新字符串。

返回值说明

返回TEXT类型。

示例

CREATE TABLE animal_test (
    animal text,
    color text
);

INSERT INTO animal_test
    VALUES ('狗', '白色'), ('猫', '白色'), ('老虎', '黄色');

SELECT
    animal,
    REPLACE(color, '白', '五彩斑斓')
FROM
    animal_test;

返回结果如下。

animal |  replace   
-------------------
狗     | 五彩斑斓色
猫     | 五彩斑斓色
老虎   | 黄色

描述：通过POSIX正则表达式将子字符串替换为新的子字符串。

REGEXP_REPLACE(<str> TEXT, <regex> TEXT, <replacement> TEXT [, <flags> TEXT]) 

参数说明

• str：必填，待替换的目标字符串。

• regex：必填，正则表达式。

• replacement：必填，用于替换匹配文本的字符串。

• flags：可选，用于指定正则表达式的匹配标志。

返回值说明

返回TEXT类型。

示例

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

描述：对字符串进行逆序。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。

• 将字符串从指定开始到结束位置的字符串进行逆序。

  PLVSTR.RVRS(<str> TEXT, <start> INTEGER, <end> INTEGER)

• 将字符串从指定位置开始到字符串结束进行逆序。

  PLVSTR.RVRS(<str> TEXT, <start> INTEGER)

• 将整个字符串逆序。

  PLVSTR.RVRS(<str> TEXT)

返回值说明

返回TEXT类型。

示例

• 示例1：将字符串从指定开始到结束位置的字符串进行逆序。

  -- 返回结果：rg
  SELECT PLVSTR.RVRS('Hologres', 5,6);

• 示例2：将字符串从指定位置开始到字符串结束进行逆序。

  -- 返回结果：sergo
  SELECT PLVSTR.RVRS('Hologres', 4);

• 示例3：将整个字符串逆序。

  -- 返回结果：sergoloH
  SELECT PLVSTR.RVRS('Hologres');

描述：获取字符串的字符长度。

CHAR_LENGTH(<str> TEXT)

返回值说明

返回TEXT类型。

示例

--返回结果：4
SELECT CHAR_LENGTH('jose');

描述：获取字符串的字节长度，使用UTF8编码字符集时，一个汉字是3字节，一个数字或字母算一个字节。

LENGTH(<str> TEXT)

返回值说明

返回TEXT类型。

示例

--返回结果：4
SELECT LENGTH('jose');

描述：支持将字符串转换为小写格式。

LOWER(<str> TEXT)

返回值说明

返回TEXT类型。

示例

-- 返回结果：tom
SELECT LOWER('TOM');

描述：支持将字符串转换为大写格式。

UPPER(<str> TEXT)

返回值说明

返回TEXT类型。

示例

-- 返回结果：TOM
SELECT UPPER('tom');

描述：支持将每个单词的第一个字母转换为大写，其余字母转换为小写。

INITCAP(<str> TEXT)

返回值说明

返回TEXT类型。

示例

-- 返回结果：Hi Thomas
SELECT INITCAP('hi THOMAS');

描述：支持获取字符串的字节数。

OCTET_LENGTH(<str> TEXT)

返回值说明

返回INT类型。

示例

--返回结果：4
SELECT OCTET_LENGTH('jose');

描述：查找子字符串在目标字符串中的位置。

POSITION(<substr> TEXT IN <str> TEXT)

参数说明

• substr：必填，子字符串。

• str：目标字符串。

返回值说明

返回INT类型。

示例

--返回结果：3
SELECT POSITION('om' IN 'Thomas');

描述：查找子字符串在目标字符串中的位置。

STRPOS(<str> TEXT, <substr> TEXT)

参数说明

• str：必填，目标字符串。

• substr：必填，子字符串。

返回值说明

返回INT类型。

示例

--返回结果：2
SELECT STRPOS('high', 'ig');

描述：支持在目标字符串指定区间获取子字符串（substring）的位置，若获取到则返回子字符串（substring）在字符串中位置，若没有获取到，则返回0。该函数是Oracle兼容函数，需要先安装orafce扩展，具体请参见Oracle兼容函数。

INSTR(<str> TEXT, <patt> TEXT [, <start> INTEGER, <nth> INTEGER])

参数说明

• str：必填，目标字符串。

• patt ：必填，待匹配的子字符串。

• start：可选，匹配的起始位置，若未配置该参数，默认从头开始匹配。

• nth：可选，匹配第几次出现的位置，若未配置该参数，默认是第一次出现的位置。

返回值说明

返回INT类型。

示例

• 示例1:

  --返回结果：4
  SELECT INSTR('Hologres', 'o',1,2);

• 示例2

  --返回结果：2
  SELECT INSTR('Hologres', 'o',1,1);

• 示例3

  --返回结果：4
  SELECT INSTR('Hologres', 'o',4);

• 示例4

  --返回结果：2
  SELECT INSTR('Hologres', 'o');

描述：计算字符串的MD5哈希值。结果表示为十六进制的形式。

MD5(<str> TEXT)

返回值说明

返回TEXT类型。

示例

--返回结果：900150983cd24fb0d6963f7d28e17f72
SELECT MD5('abc');

描述：用于解析一个符合SQL标识符规则的字符串。

PARSE_IDENT(<quali_iden> TEXT [,...] )

参数说明

• parse_ident：必填，待解析的字符串，通常是一个数据库对象的完全限定标识符。

返回值说明

返回TEXT类型。

示例

--返回结果：{"SomeSchema","sometable"} 
SELECT PARSE_IDENT('"SomeSchema".someTable');

描述：使用字符串作为合法的SQL标识符。

QUOTE_IDENT(<str> TEXT)

返回值说明

返回TEXT类型。

示例

--返回结果："Foo bar"
SELECT QUOTE_IDENT('Foo bar');

描述：将字符串转换为合法的SQL语句字符串的常量形式。

QUOTE_LITERAL(<str> TEXT)

返回值说明

返回TEXT类型。

示例

--返回结果：'O''Reilly'
SELECT QUOTE_LITERAL(E'O\'Reilly');

描述：获取字符串第一个字符的ASCII码。

ASCII(<str> TEXT)

返回值说明

返回TEXT类型。

示例

--返回结果：120
SELECT ASCII('x');

描述：获取指定编码值对应的字符。

CHR(<num> INT)

返回值说明

返回TEXT类型。

示例

--返回结果：A
SELECT CHR(65);

描述：按照设置的次数将字符串重复输出。

REPEAT(<str> TEXT, <number> INT)

参数说明

• str：必填，目标字符串。

• number：必填，目标字符串重复的次数。

返回值说明

返回TEXT类型。

示例

--返回结果：PgPgPgPg
SELECT REPEAT('Pg', 4);

描述：确认目标字符串是否以指定前缀开头。

STARTS_WITH(<str> TEXT, <prefix> TEXT)

参数说明

• str：必填，目标字符串。

• prefix：必填，前缀字符串。

返回值说明

返回BOOLEAN类型。若目标字符串是以prefix为前缀，若是则返回t（true），否则返回f（false）。

示例

--返回结果：t
SELECT STARTS_WITH('alphabet', 'alph');

描述：字符串分割函数，将分割出的数据转换成行，可作为行转列使用。

REGEXP_SPLIT_TO_TABLE(<str> TEXT, <pattern> TEXT)

参数说明

• str：必填，待分割的字符串。

• pattern：必填，定义了如何分割字符串的正则表达式。正则表达式中的特殊字符和构造将用于识别字符串中需要作为分隔点的位置。

返回值说明

返回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(<str> TEXT, <pattern> TEXT)

参数说明

• str：必填，待分割的字符串。

• pattern：必填，定义了如何分割字符串的正则表达式。正则表达式中的特殊字符和构造将用于识别字符串中需要作为分隔点的位置。

返回值说明

返回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
----------------------------
张三 | {唱歌,跳舞}
李四 | {踢球,跑步,画画}
王五 | {插花,书法,弹琴,睡觉}

描述：将数字转换为十六进制的表示形式。

TO_HEX(<number> INT OR BIGINT)

返回值说明

返回TEXT类型。

示例

--返回结果：7fffffff
SELECT TO_HEX(2147483647);

描述：支持将字符串中的字符替换为指定字符。

TRANSLATE(<str> TEXT, FROM <substr> TEXT, TO <newstr> TEXT)

参数说明

• str：必填，目标字符串。

• substr：必填，目标字符串中被替换的子字符串。

• newstr：必填，替换的新字符串。

返回值说明

返回TEXT类型。

示例

--返回结果：a2x5
SELECT TRANSLATE('12345', '143', 'ax');

描述：返回公式所在的行号。

ROW()

使用说明：仅Hologres V1.3及以上版本支持该函数。

返回值说明

返回ARRAY或TEXT类型。

示例

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