Hologres:文字列関数

文字列の連結、分割、パディング

その他の文字列関数

STRING \|\| STRING

2 つの文字列を連結します。

構文

<string_value1> TEXT || <string_value2> TEXT

パラメーター

戻り値の型： TEXT

例

-- 戻り値: HologreSQL
SELECT 'Holo' || 'greSQL';

CONCAT

2 つ以上の文字列を 1 つに連結します。NULL の引数は無視されます。

構文

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

パラメーター

戻り値の型： TEXT

例

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

結果：

concat
--------
abcde222

CONCAT_WS

最初の引数を区切り文字として使用し、それ以降のすべての引数を連結します。

構文

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

any 型は、すべてのデータ型が受け入れられることを示します。

パラメーター

必須：

sep: 各文字列の間に挿入する区切り文字です。sep が NULL の場合、この関数は NULL を返します。

オプション：

str：連結する文字列。NULL の文字列引数は無視されます。

戻り値の型： TEXT

例

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

結果：

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

LISTAGG

複数の行にわたる同じ列の値を 1 つの文字列に集約します。これは Oracle 互換の関数であり、orafce 拡張機能が必要です。設定手順については、「Oracle 互換関数」をご参照ください。

構文

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

パラメーター

戻り値の型： TEXT

例

-- 戻り値: hologres
SELECT LISTAGG(t) FROM (VALUES('holo'), ('gres')) AS l(t);

-- 戻り値: holo.gres
SELECT LISTAGG(t, '.') FROM (VALUES('holo'), ('gres')) AS l(t);

STRING_AGG

複数の行にわたる同じ列の値を 1 つの文字列に集約します。

構文

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

FILTER 句は Hologres V1.3 以降でサポートされています。インスタンスをアップグレードするには、「インスタンスのアップグレード」をご参照ください。サポートが必要な場合は、Hologres DingTalk グループにご参加ください。参加方法については、「オンラインサポートの利用方法」をご参照ください。

パラメーター

戻り値の型： TEXT

例

例 1：すべての都市名を 1 つの文字列に集約します。

CREATE TABLE city_test (
    country text,
    city text
);

INSERT INTO city_test
    VALUES ('China', 'Shanghai'), ('China', 'Taiwan'), ('Japan', 'Tokyo'), ('France', 'Paris'), ('UK', 'London');

SELECT STRING_AGG(city, ',') FROM city_test;

結果：

string_agg
-------------------------------
上海,台湾,東京,パリ,ロンドン

例 2: student_id > 2 である名前を集計します。

CREATE TABLE name_text (
    student_id int,
    name text
);

INSERT INTO name_text
    VALUES (1, 'Zhang San'), (2, 'Li Si'), (3, 'Wang Wu'), (4, 'Zhou Liu');

SELECT STRING_AGG(name, ',') FILTER (WHERE student_id > 2) FROM name_text;

結果：

string_agg
-----------
Wang Wu,Zhou Liu

LEFT

文字列の先頭から指定された文字数を抽出します。

構文

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

パラメーター

戻り値の型： TEXT

例

SELECT LEFT('hologres', 4);

結果：

left
----
holo

RIGHT

文字列の末尾から指定された文字数を抽出します。

構文

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

パラメーター

戻り値の型： TEXT

例

SELECT RIGHT('hologres', 4);

結果：

right
-----
gres

SPLIT_PART

文字列をデリミタで分割し、*n* 番目のセグメントを返します。

構文

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

パラメーター

戻り値の型： 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

SUBSTRING

対象文字列から部分文字列を抽出します。位置ベース、POSIX 正規表現、SQL 正規表現の 3 つの形式をサポートしています。

構文

位置ベース：

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)

パラメーター

戻り値の型： 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

SUBSTR

開始位置から部分文字列を抽出します。これは Oracle 互換の関数であり、orafce 拡張機能が必要です。設定手順については、「Oracle 互換関数」をご参照ください。

構文

開始位置から末尾まで抽出：

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

固定長の部分文字列を抽出：

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

パラメーター

戻り値の型： TEXT

例

例 1：4 番目の文字から末尾まで抽出します。

-- 戻り値: ogres
SELECT SUBSTR('Hologres', 4);

例 2：2 番目の文字から 5 文字を抽出します。

-- 戻り値: ologr
SELECT SUBSTR('Hologres', 2, 5);

REGEXP_MATCH

文字列を正規表現と照合し、一致した部分を配列として返します。

構文

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

パラメーター

戻り値の型： ARRAY

例

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

結果：

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

REGEXP_SPLIT_TO_TABLE

正規表現を使用して文字列を分割し、各セグメントを個別の行として返します。この関数を使用して、区切られた値を複数の行に展開します。

構文

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

パラメーター

戻り値の型： TEXT (セグメントごとに 1 行)

例

CREATE TABLE interests_sample (
    name text,
    interests text
);

INSERT INTO interests_sample
    VALUES ('田中一郎', '歌,ダンス'), ('鈴木二郎', 'サッカー,ランニング,描画'), ('佐藤三郎', '華道,書道,ピアノ演奏,睡眠');

SELECT name, REGEXP_SPLIT_TO_TABLE(interests, ',') FROM interests_sample;

結果：

名前      | regexp_split_to_table
----------+----------------------
Zhang San | 歌唱
Zhang San | ダンス
Li Si     | サッカー
Li Si     | ランニング
Li Si     | 描画
Wang Wu   | 華道
Wang Wu   | 書道
Wang Wu   | ピアノ演奏
Wang Wu   | 睡眠

REGEXP_SPLIT_TO_ARRAY

正規表現を使用して文字列を分割し、セグメントを配列として返します。

構文

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

パラメーター

戻り値の型： ARRAY

例

CREATE TABLE interests_test (
    name text,
    intrests text
);

INSERT INTO interests_test
    VALUES ('Zhang San', 'singing,dancing'), ('Li Si', 'soccer,running,drawing'), ('Wang Wu', 'flower arranging,calligraphy,playing piano,sleeping');

SELECT name, REGEXP_SPLIT_TO_ARRAY(intrests, ',') FROM interests_test;

結果：

名前      | regexp_split_to_array
----------+----------------------------------------------------------
Zhang San | {歌唱,ダンス}
Li Si     | {サッカー,ランニング,描画}
Wang Wu   | {"フラワーアレンジメント",書道,"ピアノ演奏",睡眠}

TRIM

文字列の一方または両方の端から指定された文字を削除します。

構文

TRIM([LEADING | TRAILING | BOTH] [<characters> TEXT] FROM <string> TEXT)

パラメーター

戻り値の型： TEXT

例

-- 戻り値: Tom
SELECT TRIM(BOTH 'xyz' FROM 'yxTomxx');

BTRIM

文字列の両端から指定された文字を削除します。

構文

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

パラメーター

戻り値の型： TEXT

例

-- 戻り値: trim
SELECT BTRIM('xyxtrimyyx', 'xyz');

LTRIM

文字列の左側 (先頭) から指定された文字を削除します。

構文

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

パラメーター

戻り値の型： TEXT

例

-- 戻り値: test
SELECT LTRIM('zzzytest', 'xyz');

RTRIM

文字列の右側 (末尾) から指定された文字を削除します。

構文

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

パラメーター

戻り値の型： TEXT

例

-- 戻り値: test
SELECT RTRIM('testxxzx', 'xyz');

DECIMAL 値を TEXT にキャストすると、結果はそのスケールで定義された末尾のゼロを保持します。たとえば 2 の代わりに 2.00 となります。ゼロのみを削除する単一の RTRIM 呼び出しでは、小数点が残ります：

-- 戻り値: 2.
SELECT RTRIM(2.00::DECIMAL::TEXT, '0');

末尾のゼロと小数点を一緒に削除するには、2 つの RTRIM 呼び出しをネストします。最初の呼び出しでゼロを削除し、2 番目の呼び出しで小数点を削除します。

-- 戻り値: 2
SELECT RTRIM(RTRIM(2.00::DECIMAL::TEXT, '0'), '.');

次の表は、a という名前の DECIMAL(10,2) 列のさまざまな値に対する 2 つの式の結果を比較したものです：

LPAD

文字列を指定された全長になるように、埋め込み文字で左パディングします。

構文

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

パラメーター

戻り値の型： TEXT

例

-- 戻り値: xyxhi
SELECT LPAD('hi', 5, 'xy');

RPAD

文字列を指定された全長になるように、埋め込み文字で右パディングします。

構文

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

パラメーター

戻り値の型： TEXT

例

-- 戻り値: hixyx
SELECT RPAD('hi', 5, 'xy');

ASCII

文字列の最初の文字の ASCII コードを返します。

構文

ASCII(<str> TEXT)

戻り値の型： INTEGER

例

-- 戻り値: 120
SELECT ASCII('x');

CHAR_LENGTH

文字列の文字数を返します。LENGTH と同等です。

構文

LENGTH(<str> TEXT)

戻り値の型： INTEGER

例

-- 戻り値: 4
SELECT CHAR_LENGTH('jose');

LENGTH

文字列の文字数を返します。UTF-8 エンコーディングでは、各漢字、数字、または文字は 1 文字としてカウントされます。CHAR_LENGTH と同等です。

構文

LENGTH(<str> TEXT)

戻り値の型： INTEGER

例

-- 戻り値: 4
SELECT LENGTH('jose');

CHR

指定された ASCII または Unicode のコード値に対応する文字を返します。

構文

CHR(<num> INT)

戻り値の型： TEXT

例

-- 戻り値: A
SELECT CHR(65);

INITCAP

各単語の最初の文字を大文字に、残りを小文字に変換します。単語とは、非英数字で区切られた英数字のシーケンスです。

構文

INITCAP(<str> TEXT)

戻り値の型： TEXT

例

-- 戻り値: Hi Thomas
SELECT INITCAP('hi THOMAS');

LOWER

文字列を小文字に変換します。

構文

LOWER(<str> TEXT)

戻り値の型： TEXT

例

-- 戻り値: tom
SELECT LOWER('TOM');

UPPER

文字列を大文字に変換します。

構文

UPPER(<str> TEXT)

戻り値の型： TEXT

例

-- 戻り値: TOM
SELECT UPPER('tom');

OCTET_LENGTH

文字列のバイト数を返します。

構文

OCTET_LENGTH(<str> TEXT)

戻り値の型： INTEGER

例

-- 戻り値: 4
SELECT OCTET_LENGTH('jose');

MD5

文字列の MD5 ハッシュを計算し、結果を 16 進数文字列として返します。

構文

MD5(<str> TEXT)

戻り値の型： TEXT

例

-- 戻り値: 900150983cd24fb0d6963f7d28e17f72
SELECT MD5('abc');

POSITION

文字列内の部分文字列の開始位置を返します。部分文字列が見つからない場合は 0 を返します。STRPOS と同等です。

構文

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

パラメーター

戻り値の型： INTEGER

例

-- 戻り値: 3
SELECT POSITION('om' IN 'Thomas');

STRPOS

文字列内の部分文字列の開始位置を返します。部分文字列が見つからない場合は 0 を返します。POSITION と同等です。

構文

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

パラメーター

戻り値の型： INTEGER

例

-- 戻り値: 2
SELECT STRPOS('high', 'ig');

INSTR

対象文字列内の部分文字列の位置を返します。開始位置と出現回数をオプションで制御できます。部分文字列が見つからない場合は 0 を返します。これは Oracle 互換の関数であり、orafce 拡張機能が必要です。設定手順については、「Oracle 互換関数」をご参照ください。

構文

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

パラメーター

戻り値の型： INTEGER

例

-- 戻り値: 4 (位置 1 から検索して 2 番目に出現する 'o')
SELECT INSTR('Hologres', 'o', 1, 2);

-- 戻り値: 2 (位置 1 から検索して 1 番目に出現する 'o')
SELECT INSTR('Hologres', 'o', 1, 1);

-- 戻り値: 4 (位置 4 以降で最初に出現する 'o')
SELECT INSTR('Hologres', 'o', 4);

-- 戻り値: 2 (最初に出現する 'o')
SELECT INSTR('Hologres', 'o');

REPLACE

文字列内の部分文字列のすべての出現箇所を置換します。

構文

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

パラメーター

戻り値の型： TEXT

例

CREATE TABLE animal_test (
    animal text,
    color text
);

INSERT INTO animal_test
    VALUES ('dog', 'white'), ('cat', 'white'), ('tiger', 'yellow');

SELECT animal, REPLACE(color, 'white', 'multi-colored') FROM animal_test;

結果：

animal | replace
-------+--------------
犬     | 多色
猫     | 多色
虎     | 黄

REGEXP_REPLACE

POSIX 正規表現に一致する部分文字列を置換文字列で置換します。

構文

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

パラメーター

必須：

オプション：

flags：照合動作を制御する 1 つ以上の文字。デフォルト：最初の一致のみを置換し、大文字と小文字を区別します。

戻り値の型： 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

TRANSLATE

文字マッピングに基づいて文字列内の個々の文字を置換します。

構文

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

パラメーター

戻り値の型： TEXT

例

-- 戻り値: a2x5
SELECT TRANSLATE('12345', '143', 'ax');

REPEAT

文字列を指定された回数繰り返します。

構文

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

パラメーター

戻り値の型： TEXT

例

-- 戻り値: PgPgPgPg
SELECT REPEAT('Pg', 4);

STARTS_WITH

文字列が指定されたプレフィックスで始まる場合に true を返します。

構文

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

パラメーター

戻り値の型： BOOLEAN (t は true、f は false)

例

-- 戻り値: t
SELECT STARTS_WITH('alphabet', 'alph');

PARSE_IDENT

完全修飾 SQL 識別子文字列を解析し、そのコンポーネントを配列として返します。引用符で囲まれていないコンポーネントは小文字に変換され、引用符で囲まれたコンポーネントは大文字と小文字が維持されます。

構文

PARSE_IDENT(<quali_iden> TEXT)

パラメーター

戻り値の型： TEXT (配列形式)

例

-- 戻り値: {"SomeSchema","sometable"}
SELECT PARSE_IDENT('"SomeSchema".someTable');

QUOTE_IDENT

有効な SQL 識別子として引用符で囲まれた文字列を返します。必要に応じて二重引用符を追加します (たとえば、文字列にスペースや、そうでなければ小文字に変換される大文字が含まれている場合)。

構文

QUOTE_IDENT(<str> TEXT)

戻り値の型： TEXT

例

-- 戻り値: "Foo bar"
SELECT QUOTE_IDENT('Foo bar');

QUOTE_LITERAL

有効な SQL 文字列定数として引用符で囲まれた文字列を返します。必要に応じて単一引用符をエスケープします。

構文

QUOTE_LITERAL(<str> TEXT)

戻り値の型： TEXT

例

-- 戻り値: 'O''Reilly'
SELECT QUOTE_LITERAL(E'O\'Reilly');

TO_HEX

整数をその 16 進数表現に変換します。

構文

TO_HEX(<number> INT | BIGINT)

戻り値の型： TEXT

例

-- 戻り値: 7fffffff
SELECT TO_HEX(2147483647);

TO_NUMBER

フォーマットモデルを使用して、フォーマットされた文字列を数値に変換します。

構文

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

パラメーター

戻り値の型： NUMERIC

例

-- 戻り値: -12454.8
SELECT TO_NUMBER('12,454.8-', '99G999D9S');

PLVSTR.RVRS

文字列の一部または全部を反転させます。これは Oracle 互換の関数であり、orafce 拡張機能が必要です。設定手順については、「Oracle 互換関数」をご参照ください。

構文

文字列全体を反転：

PLVSTR.RVRS(<str> TEXT)

開始位置から末尾まで反転：

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

指定された範囲を反転：

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

パラメーター

戻り値の型： TEXT

例

-- 戻り値: sergoloH (文字列全体を反転)
SELECT PLVSTR.RVRS('Hologres');

-- 戻り値: sergo (位置 4 から末尾まで反転)
SELECT PLVSTR.RVRS('Hologres', 4);

-- 戻り値: rg (位置 5-6 を反転)
SELECT PLVSTR.RVRS('Hologres', 5, 6);

ROW

現在の行の行番号を返します。Hologres V1.3 以降でサポートされています。

インスタンスを V1.3 以降にアップグレードするには、「インスタンスのアップグレード」をご参照ください。サポートが必要な場合は、Hologres DingTalk グループにご参加ください。参加方法については、「オンラインサポートの利用方法」をご参照ください。

構文

ROW(<val1>, <val2>, ...)

戻り値の型： ARRAY または TEXT

例

次の例では、列 a と列 b の値が等しい行を返します。

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