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])

• 從字串中找出與POSIXRegex匹配的子字串。

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

• 從字串中找出與SQLRegex匹配的子字串。

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

參數說明

• str：必填，目標字串。

• num_start和num_end：可選，起始和結束位置。

• pattern：必填，Regex，用於定義如何匹配和提取字串中的子串。

• escape：必填，逸出字元。

  說明

  在Regex中，某些字元如.、*、+等具有特殊意義。如果需要在文本中匹配這些字元本身，而不是它們的特殊含義，就需要在它們前面加上逸出字元。

傳回值說明

返回TEXT類型。

樣本

• 樣本1：從字串中找出指定位置的子字串。

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

  返回結果如下。

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

• 樣本2：從字串中找出與POSIXRegex匹配的子字串。

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

  返回結果如下。

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

• 樣本3：從字串中找出與SQLRegex匹配的子字串。

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

  返回結果如下。

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

描述：對字串按Regex進行匹配，如果存在則會在結果數組中表示出來。

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

參數說明

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

• pattern：必填，Regex。

傳回值說明

返回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   
-------------------
狗     | 五彩斑斕色
貓     | 五彩斑斕色
老虎   | 黃色

描述：通過POSIXRegex將子字串替換為新的子字串。

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

參數說明

• str：必填，待替換的目標字串。

• regex：必填，Regex。

• replacement：必填，用於替換匹配文本的字串。

• flags：可選，用於指定Regex的匹配標誌。

傳回值說明

返回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：必填，定義了如何分割字串的Regex。Regex中的特殊字元和構造將用於識別字串中需要作為分隔點的位置。

傳回值說明

返回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
---------------------------
張三 | 唱歌
張三 | 跳舞
李四 | 踢球
李四 | 跑步
李四 | 畫畫
王五 | 插花
王五 | 書法
王五 | 彈琴
王五 | 睡覺

描述：字串分割函數，按Regex對字串進行分割並轉換成數組。

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

參數說明

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

• pattern：必填，定義了如何分割字串的Regex。Regex中的特殊字元和構造將用於識別字串中需要作為分隔點的位置。

傳回值說明

返回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