Lindorm SQL でサポートされている文字列関数 - Lindorm - Alibaba Cloud ドキュメントセンター

このトピックでは、Lindorm SQL でサポートされている文字列関数について説明し、関数の使用方法の例を示します。

該当するエンジンとバージョン

このトピックで説明する文字列関数は、LindormTable 2.5.1.1 以降のバージョンにのみ適用されます。

説明

LindormTable のバージョンを表示およびアップグレードする方法の詳細については、「LindormTable のリリースノート」および「Lindorm インスタンスのマイナーエンジンバージョンをアップグレードする」をご参照ください。

サポートされている文字列関数

次の表に、Lindorm SQL でサポートされている文字列関数を示します。

関数	説明
CONCAT	複数の文字列を連結して新しい文字列を作成します。
LENGTH	文字列の長さを計算します。
LOWER	文字列内のすべての文字を小文字に変換します。
MD5	文字列の MD5 ハッシュを計算します。
SHA256	文字列の SHA256 ハッシュを計算します。
REPLACE	指定されたルールに一致する部分文字列を新しい部分文字列に置き換えます。
REVERSE	文字列を反転します。
REGEXP_REPLACE	文字列の指定された位置から、指定されたルールに一致する部分文字列を新しい部分文字列に置き換えます。
REGEXP_SUBSTR	文字列の指定された位置から始まり、指定されたルールに一致する部分文字列を返します。
SUBSTR	指定された長さの部分文字列を返します。
START_WITH	文字列のプレフィックスが指定された文字列と一致するかどうかを判断します。
TRIM	文字列の先頭と末尾のスペースを削除します。
UPPER	文字列内のすべての文字を大文字に変換します。
MATCH	指定された列の値が指定されたルールと一致するかどうかを確認します。

CONCAT

CONCAT 関数は、複数の文字列を連結して新しい文字列を作成します。

構文

CONCAT('string1','string2',...,'stringN')

パラメーター

パラメーター	必須かどうか	説明
'string1','string2',...,'stringN'	はい	連結する文字列。

例

a、b、c の文字列を連結して文字列 abc を作成します。

SELECT concat('a','b','c') AS val;

次の結果が返されます。

+--------+
| val    |
+--------+
| abc    |
+--------+

区切り文字を含まない連結された文字列が返されます。

LENGTH

LENGTH 関数は、文字列の長さを計算します。

構文

LENGTH('string')

パラメーター

パラメーター	必須かどうか	説明
string	はい	処理する文字列。

例

文字列 abc の長さを計算します。

SELECT length('abc') AS len;

次の結果が返されます。

+-----+
| len |
+-----+
| 3   |
+-----+

戻り値 3 は、文字列 abc の長さが 3 文字であることを示します。

LOWER

LOWER 関数は、文字列内のすべての文字を小文字に変換します。

構文

LOWER('string')

パラメーター

パラメーター	必須かどうか	説明
string	はい	変換する文字列。

例

例 1：文字列 ABC 内のすべての文字を小文字に変換します。
```
SELECT lower('ABC') AS val;
```
次の結果が返されます。
```
+--------+
| val    |
+--------+
| abc    |
+--------+
```
文字列 abc が返されます。元の文字列のすべての文字が小文字に変換されます。
例 2：文字列 Abc 内のすべての文字を小文字に変換します。
```
SELECT lower('Abc') AS val;
```
次の結果が返されます。
```
+--------+
| val    |
+--------+
| abc    |
+--------+
```
文字列 abc が返されます。元の文字列の大文字がすべて小文字に変換されます。

MD5

MD5 関数は、文字列の MD5 ハッシュを計算します。

構文

MD5('string')

パラメーター

パラメーター	必須かどうか	説明
string	はい	処理する文字列。

例

文字列 abc の MD5 ハッシュを計算します。

SELECT md5('abc') AS val;

次の結果が返されます。

+----------------------------------+
|               val                |
+----------------------------------+
| 900150983cd24fb0d6963f7d28e17f72 |
+----------------------------------+

文字列 abc の MD5 ハッシュが返されます。

SHA256

SHA256 関数は、文字列の SHA256 ハッシュを計算します。

構文

SHA256('string')

パラメーター

パラメーター	必須かどうか	説明
string	はい	処理する文字列。

例

この例では、次の文を実行してサンプルテーブルを作成し、データを入力します。

-- サンプルテーブルを作成します。
CREATE TABLE tb (id int, name varchar, address varchar, PRIMARY KEY(id, name)); 

-- 作成したテーブルにデータを挿入します。
UPSERT INTO tb (id, name, address) VALUES (1, 'jack',  'hz');

id が 1 の行の name 列の値の SHA256 ハッシュをクエリします。

SELECT sha256(name) AS sc FROM tb WHERE id=1;

次の結果が返されます。

+------------------------------------------------------------------+
|                                sc                                |
+------------------------------------------------------------------+
| 31611159e7e6ff7843ea4627745e89225fc866621cfcfdbd40871af4413747cc |
+------------------------------------------------------------------+

文字列 jack の SHA256 ハッシュが返されます。

REPLACE

REPLACE 関数は、指定されたルールに一致する部分文字列を新しい部分文字列に置き換えます。

構文

REPLACE('string','from_str','to_str')

パラメーター

パラメーター	必須かどうか	説明
string	はい	処理する文字列。
from_str	はい	置き換える部分文字列。
to_str	はい	ルールに一致する部分文字列を置き換える部分文字列。

例

例 1：文字列 abc 内の、部分文字列 bc に一致するすべての部分文字列を、部分文字列 cd に置き換えます。
```
SELECT replace('abc','bc','cd') AS val;
```
次の結果が返されます。
```
+-----+
| val |
+-----+
| acd |
+-----+
```
文字列 acd が返されます。文字列 abc 内の、部分文字列 bc が、部分文字列 cd に置き換えられます。
例 2：文字列 abcbc 内の、部分文字列 bc に一致するすべての部分文字列を、部分文字列 cd に置き換えます。
```
SELECT replace('abcbc', 'bc', 'cd') AS val;
```
次の結果が返されます。
```
+-------+
|  val  |
+-------+
| acdcd |
+-------+
```
文字列 acdcd が返されます。文字列 abcbc 内のすべての部分文字列 bc が、部分文字列 cd に置き換えられます。

REVERSE

REVERSE 関数は、文字列を反転します。

構文

REVERSE('string')

パラメーター

パラメーター	必須かどうか	説明
string	はい	処理する文字列。

例

文字列 abc を反転します。

SELECT reverse('abc') AS val;

次の結果が返されます。

+-----+
| val |
+-----+
| cba |
+-----+

文字列 cba が返されます。この文字列は、文字列 abc を反転したものです。

REGEXP_REPLACE

REGEXP_REPLACE 関数は、文字列の指定された位置から、指定されたルールに一致する部分文字列を新しい部分文字列に置き換えます。

構文

REGEXP_REPLACE('string',pat,rep,[pos])

パラメーター

パラメーター	必須かどうか	説明
string	はい	処理する文字列。
pattern	はい	一致ルールを指定するために使用する正規表現。
rep	はい	ルールに一致する部分文字列を置き換える部分文字列。
position	いいえ	ルールに基づいて置き換える部分文字列を確認する開始位置。このパラメーターの値は、1 以上の整数です。このパラメーターを指定しない場合、関数は文字列の最初の文字から始まる部分文字列をルールに基づいて確認します。

例

例 1：pos パラメーターを指定しません。この場合、関数は文字列の最初の文字から始まる部分文字列を確認し、部分文字列 b に一致する部分文字列を部分文字列 c に置き換えます。
```
SELECT regexp_replace('abc', 'b', 'c') AS val;
```
次の結果が返されます。
```
+-----+
| val |
+-----+
| acc |
+-----+
```
文字列 acc が返されます。部分文字列 b に一致する部分文字列が、部分文字列 c に置き換えられます。
例 2：pos パラメーターを 2 に設定します。この場合、関数は文字列の 2 番目の文字から始まる部分文字列を確認し、部分文字列 b に一致する部分文字列を部分文字列 c に置き換えます。
```
SELECT regexp_replace('abcbc', 'b', 'c', 2) AS val;
```
次の結果が返されます。
```
+-------+
|  val  |
+-------+
| acccc |
+-------+
```

文字列 acccc が返されます。関数は、文字列 abcbc 内の 2 番目の文字から始まり、部分文字列 b に一致する部分文字列を確認し、それらの部分文字列を部分文字列 c に置き換えます。

例 3：pos パラメーターを 3 に設定します。この場合、関数は文字列の 3 番目の文字から始まる部分文字列を確認し、部分文字列 b に一致する部分文字列を部分文字列 c に置き換えます。
```
SELECT regexp_replace('abcbc', 'b', 'c', 3) AS val;
```
次の結果が返されます。
```
+-------+
|  val  |
+-------+
| abccc |
+-------+
```
文字列 abccc が返されます。関数は、文字列 abcbc 内の 3 番目の文字から始まり、部分文字列 b に一致する部分文字列を確認し、それらの部分文字列を部分文字列 c に置き換えます。

REGEXP_SUBSTR

REGEXP_SUBSTR 関数は、文字列の指定された位置から始まり、指定されたルールに一致する部分文字列を返します。

構文

REGEXP_SUBSTR('string', pat, [pos])

パラメーター

パラメーター	必須かどうか	説明
string	はい	処理する文字列。
pattern	はい	一致ルールを指定するために使用する正規表現。
position	いいえ	ルールに基づいて置き換える部分文字列を確認する開始位置。このパラメーターの値は、1 以上の整数です。このパラメーターを指定しない場合、関数は文字列の最初の文字から始まる部分文字列をルールに基づいて確認します。

例

例 1：pos パラメーターを 3 に設定します。この場合、関数は、文字列 abc の 3 番目の文字から始まる部分文字列が、ルールで指定された部分文字列 b と一致するかどうかを確認します。
```
 SELECT regexp_substr('abc', 'b', 3) AS val;
```
次の結果が返されます。
```
+-----+
| val |
+-----+
|     |
+-----+
```
値が返されません。これは、文字列 abc の 3 番目の文字から始まる部分文字列が、部分文字列 b と一致しないことを示します。
例 2：pos パラメーターを指定しません。この場合、関数は、文字列 abc の最初の文字から始まる部分文字列が、ルールで指定された部分文字列 b と一致するかどうかを確認します。
```
SELECT regexp_substr('abc', 'b') AS val;
```
次の結果が返されます。
```
+-----+
| val |
+-----+
| b   |
+-----+
```
部分文字列 b が返されます。この部分文字列は、文字列 abc の最初の文字の後に切り捨てられ、ルールに一致します。

SUBSTR

SUBSTR 関数は、指定された長さの部分文字列を返します。

構文

SUBSTR( string, pos, [len])

パラメーター

パラメーター	必須かどうか	説明
string	はい	処理する文字列。
position	はい	部分文字列を切り捨てる開始位置。このパラメーターの値は、1 以上の整数です。
len	いいえ	切り捨てる部分文字列の長さ。このパラメーターの値は、1 以上の整数です。このパラメーターを指定しない場合、`pos` で指定された位置から文字列の末尾までの部分文字列がデフォルトで返されます。

例

例 1：len パラメーターを指定しません。この場合、文字列 abc の 2 番目の文字から末尾までの部分文字列がデフォルトで返されます。
```
SELECT substr('abc', 2) AS val;
```
次の結果が返されます。
```
+-----+
| val |
+-----+
| bc  |
+-----+
```
部分文字列 bc が返されます。この部分文字列は、文字列 abc の 2 番目の文字から末尾までを切り捨てたものです。
例 2：len パラメーターを指定します。この場合、文字列 abc の最初の文字から 2 番目の文字までの部分文字列が返されます。
```
SELECT substr('abc', 1, 2) AS val;
```
次の結果が返されます。
```
+-----+
| val |
+-----+
| ab  |
+-----+
```
部分文字列 ab が返されます。この部分文字列は、文字列 abc の最初の文字から 2 番目の文字までを切り捨てたものです。

START_WITH

START_WITH 関数は、文字列のプレフィックスが指定された文字列と一致するかどうかを判断します。

構文

START_WITH('string1', 'string2')

パラメーター

パラメーター	必須かどうか	説明
string1	はい	処理する文字列。
string2	はい	プレフィックスの照合に使用する文字列。

例

例 1：文字列 ab が文字列 abc のプレフィックスであるかどうかを判断します。
```
SELECT start_with('abc', 'ab') AS val;
```
次の結果が返されます。
```
+--------+
| val    |
+--------+
| true   |
+--------+
```
戻り値は true です。これは、文字列 ab が文字列 abc のプレフィックスであることを示します。
例 2：文字列 bc が文字列 abc のプレフィックスであるかどうかを判断します。
```
SELECT start_with('abc', 'bc') AS val;
```
次の結果が返されます。
```
+--------+
| val    |
+--------+
| false  |
+--------+
```
戻り値は false です。これは、文字列 bc が文字列 abc のプレフィックスではないことを示します。

TRIM

TRIM 関数は、文字列の先頭と末尾のスペースを削除します。

構文

TRIM('string')

パラメーター

パラメーター	必須かどうか	説明
string	はい	処理する文字列。

例

文字列 abc の先頭と末尾のスペースを削除します。

SELECT trim(' abc    ') AS str;

次の結果が返されます。

+-----+
| str |
+-----+
| abc |
+-----+

先頭と末尾のスペースが削除された文字列が返されます。

UPPER

UPPER 関数は、文字列内のすべての文字を大文字に変換します。

構文

UPPER('string')

パラメーター

パラメーター	必須かどうか	説明
string	はい	変換する文字列。

例

例 1：文字列 abc 内のすべての文字を大文字に変換します。
```
SELECT upper('abc') AS val;
```
次の結果が返されます。
```
+--------+
| val    |
+--------+
| ABC    |
+--------+
```
文字列 ABC が返されます。元の文字列のすべての文字が大文字に変換されます。
例 2：文字列 aBC 内のすべての文字を大文字に変換します。
```
SELECT upper('aBC') AS val;
```
次の結果が返されます。
```
+--------+
| val    |
+--------+
| ABC    |
+--------+
```
文字列 ABC が返されます。元の文字列の小文字がすべて大文字に変換されます。

MATCH

MATCH 関数は、指定された列の値が指定されたルールと一致するかどうかを判断します。

重要

LindormTable 2.7.2 以降のバージョンのみが MATCH 関数をサポートしています。マイナーバージョンを更新することで、現在のバージョンから 2.7.2 以降に更新できます。

MATCH 関数は、検索インデックスでのみ機能します。クエリに MATCH 関数が含まれており、検索インデックスが存在する場合、システムは自動的に検索インデックスを使用します。

使用上の注意

MATCH 関数を使用してクエリを実行する前に、次のパフォーマンスの問題とインデックス要件に注意してください。

インデックスのない列を含むクエリの場合、システムは最初に検索インデックスを使用してインデックス付き列をクエリし、次に結果セット内のインデックスのない列を行ごとにフィルタリングします。クエリのパフォーマンスはインデックス条件のヒット率と相関しているため、large データセットでのフィルタリング操作によってパフォーマンスが低下する可能性があります。この場合、ビジネス要件に基づいて、クエリ対象のインデックスのない列を検索インデックスに追加するために列の追加文を実行することができます。
現在、プライマリテーブルとセカンダリインデックスは MATCH 関数をサポートしていません。MATCH 関数がクエリで使用される場合、システムは検索インデックスを直接使用します。プライマリテーブルまたはセカンダリインデックスに対してクエリを実行する必要がある場合は、クエリの要件が満たされている場合、クエリ文に LIKE キーワードを追加することにより、トークン化されたクエリをあいまい検索に変更できます。
重要
あいまい検索はプライマリテーブルとセカンダリインデックスで機能しますが、トークン化されたクエリよりもクエリパフォーマンスが低下します。
MATCH 関数と LIKE キーワードの両方を組み合わせたクエリの場合、検索インデックスを作成するときに、対応する列をトークン化された列として構成する必要があります。

構文

MATCH (column_identifiers) AGAINST (search_expr)

説明

MATCH 関数は、SQL クエリの WHERE 句でのみ使用できます。
デフォルトでは、MATCH 関数を含むクエリ文の戻りデータは、列の値と指定されたルールの近接性に基づいて逆順にソートされます。

パラメーター

パラメーター	必須かどうか	説明
column_identifiers	はい	指定されたルールと照合する列。複数の列を入力できます。列はコンマ（,）で区切ります。複数の列を入力した場合、列が結合された後、指定されたルールと照合されます。重要 column_identifiers パラメーターで指定された列の検索インデックスを作成する必要があります。アナライザーは、単語分割のために列に対して構成されます。詳細については、「検索インデックス機能を有効にする」および「CREATE INDEX」をご参照ください。
search_expr	はい	一致ルールとして使用される文字列定数。詳細については、このトピックの「一致ルールの説明」セクションをご参照ください。

一致ルールの説明

一致ルールは、1 つ以上の条件で構成されます。条件はスペースで区切ります。条件には、次のいずれかの項目を指定できます。

単一の単語。指定された単語を含むデータは、この条件に一致します。例：hello。
二重引用符（""）で囲まれたフレーズ。単語分割なしでフレーズ全体を含むデータがこの条件に一致します。たとえば、"hello world" は、"hello world" フレーズを含むデータがルールに一致することを示します。
括弧 (()) で囲まれた別のマッチングルール。括弧で囲まれたマッチングルールに一致するデータは、この条件に一致します。例: (another "hello world")。

条件の前にシンボルを追加することで、条件の一致動作を変更できます。

+ は、条件を満たす必要があることを示します。
- は、条件を満たすことができないことを示します。
条件の前に記号が追加されていない場合、条件は必須ではありません。ただし、条件を満たすデータは、より高いランキングを得ることができます。

例

次の例では、次の文を実行してサンプルテーブルを作成し、データを入力します。

-- サンプルテーブル tb を作成します。
CREATE TABLE tb (id INT, c1 VARCHAR, PRIMARY KEY(id)); 

-- 検索インデックスを作成します。検索インデックスを作成する前に、検索インデックス機能が有効になっていることを確認してください。
CREATE INDEX idx USING SEARCH ON tb (c1(type=text));

-- テーブルにデータを挿入します。
UPSERT INTO tb (id,c1) VALUES (1,'hello');
UPSERT INTO tb (id,c1) VALUES (2,'world');
UPSERT INTO tb (id,c1) VALUES (3,'hello world');
UPSERT INTO tb (id,c1) VALUES (4,'hello my world');
UPSERT INTO tb (id,c1) VALUES (5,'hello you');
UPSERT INTO tb (id,c1) VALUES (6,'hello you and me');
UPSERT INTO tb (id,c1) VALUES (7,'you and me');

例 1：c1 列に hello または world という単語が含まれるデータをクエリします。

SELECT * FROM tb WHERE MATCH (c1) AGAINST ('hello world');

次の結果が返されます。

+----+------------------+
| id |        c1        |
+----+------------------+
| 3  | hello world      |
| 2  | world            |
| 4  | hello my world   |
| 5  | hello you        |
| 1  | hello            |
| 6  | hello you and me |
+----+------------------+

例 2：c1 列に hello という単語が含まれている可能性があり、world という単語が必ず含まれているデータをクエリします。

SELECT * FROM tb WHERE MATCH (c1) AGAINST ('hello +world');

次の結果が返されます。

+----+----------------+
| id |       c1       |
+----+----------------+
| 3  | hello world    |
| 2  | world          |
| 4  | hello my world |
+----+----------------+

例 3：c1 列に world という単語が含まれており、hello という単語が含まれていないデータをクエリします。
```
SELECT * FROM tb WHERE MATCH (c1) AGAINST ('-hello +world');
```
次の結果が返されます。
```
+----+-------+
| id |  c1   |
+----+-------+
| 2  | world |
+----+-------+
```

例 4：c1 列に hello world というフレーズが含まれるデータをクエリします。

SELECT * FROM tb WHERE MATCH (c1) AGAINST ('"hello world"');

次の結果が返されます。

+----+-------------+
| id |     c1      |
+----+-------------+
| 3  | hello world |
+----+-------------+

例 5：c1 列に hello という単語が必ず含まれており、you と me という単語のうち少なくとも 1 つが含まれているデータをクエリします。

SELECT * FROM tb WHERE MATCH (c1) AGAINST ('+hello +(you me)');

次の結果が返されます。

+----+------------------+
| id |        c1        |
+----+------------------+
| 6  | hello you and me |
| 5  | hello you        |
+----+------------------+