文字列関数
TiDB は、MySQL 8.0 で利用可能な文字列関数のほとんどと、Oracle 21 で利用可能な関数の一部をサポートしています。
Oracle と TiDB の関数と構文の比較については、 Oracle と TiDB の機能と構文の比較参照してください。
サポートされている関数
ASCII()
ASCII(str)関数は、指定された引数の左端の文字の ASCII 値を取得するために使用されます。引数は文字列または数値のいずれかになります。
- 引数が空でない場合、関数は左端の文字の ASCII 値を返します。
- 引数が空の文字列の場合、関数は
0返します。 - 引数が
NULLの場合、関数はNULL返します。
注記：
ASCII(str)8 ビットの 2 進数 (1 バイト) を使用して表される文字に対してのみ機能します。
例：
SELECT ASCII('A'), ASCII('TiDB'), ASCII(23);
出力：
+------------+---------------+-----------+
| ASCII('A') | ASCII('TiDB') | ASCII(23) |
+------------+---------------+-----------+
| 65 | 84 | 50 |
+------------+---------------+-----------+
BIN()
BIN()関数は、指定された引数をそのバイナリ値の文字列表現に変換するために使用されます。引数は文字列または数値のいずれかになります。
- 引数が正の数の場合、関数はそのバイナリ値の文字列表現を返します。
- 引数が負の数の場合、関数は引数の絶対値をバイナリ表現に変換し、バイナリ値の各ビットを反転し（
0を1に、10に変更）、反転した値に1を加算します。 - 引数が数字のみを含む文字列の場合、関数はその数字に応じて結果を返します。たとえば、
"123"と123の結果は同じになります。 - 引数が文字列で、その最初の文字が数字ではない場合（
"q123"など）、関数は0を返します。 - 引数が数字と非数字で構成される文字列の場合、関数は引数の先頭の連続する数字に従って結果を返します。たとえば、
"123q123"と123の結果は同じですが、BIN('123q123')場合はTruncated incorrect INTEGER value: '123q123'ような警告が生成されます。 - 引数が
NULLの場合、関数はNULL返します。
例1:
SELECT BIN(123), BIN('123q123');
出力1:
+----------+----------------+
| BIN(123) | BIN('123q123') |
+----------+----------------+
| 1111011 | 1111011 |
+----------+----------------+
例2:
SELECT BIN(-7);
出力2:
+------------------------------------------------------------------+
| BIN(-7) |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111001 |
+------------------------------------------------------------------+
BIT_LENGTH()
BIT_LENGTH()関数は、指定された引数の長さをビット単位で返すために使用されます。
例:
SELECT BIT_LENGTH("TiDB");
+--------------------+
| BIT_LENGTH("TiDB") |
+--------------------+
| 32 |
+--------------------+
1文字あたり8ビット×4文字=32ビット
SELECT BIT_LENGTH("PingCAP 123");
+---------------------------+
| BIT_LENGTH("PingCAP 123") |
+---------------------------+
| 88 |
+---------------------------+
1文字あたり8ビット（スペースは英数字以外の文字なのでカウントされます）×11文字=88ビット
SELECT CustomerName, BIT_LENGTH(CustomerName) AS BitLengthOfName FROM Customers;
+--------------------+-----------------+
| CustomerName | BitLengthOfName |
+--------------------+-----------------+
| Albert Einstein | 120 |
| Robert Oppenheimer | 144 |
+--------------------+-----------------+
注記：
上記の例は、
Customersという名前のテーブルと、テーブル内にCustomerNameという名前の列を持つデータベースが存在するという前提で動作します。
CHAR()
CHAR()関数は、特定の ASCII 値に対応する文字を取得するために使用されます。これは、特定の文字の ASCII 値を返すASCII()の逆の操作を実行します。複数の引数が指定された場合、関数はすべての引数に対して動作し、それらは連結されます。
例:
SELECT CHAR(65);
+------------+
| CHAR(65) |
+------------+
| A |
+------------+
SELECT CHAR(84);
+------------+
| CHAR(84) |
+------------+
| T |
+------------+
CHAR()関数は、標準の ASCII 範囲 ( 0 - 127 ) を超える ASCII 値の対応する文字を取得するためにも使用できます。
/*For extended ASCII: */
SELECT CHAR(128);
+------------+
| CHAR(128) |
+------------+
| 0x80 |
+------------+
CHAR()関数は、Unicode 値に対応する文字値を取得することもできます。
/* For Unicode: */
--skip-binary-as-hex
SELECT CHAR(50089);
+--------------+
| CHAR(50089) |
+--------------+
| é |
+--------------+
SELECT CHAR(65,66,67);
+----------------+
| CHAR(65,66,67) |
+----------------+
| ABC |
+----------------+
1 row in set (0.00 sec)
CHAR_LENGTH()
CHAR_LENGTH()関数は、指定された引数内の文字の合計数を整数として取得するために使用されます。
例:
SELECT CHAR_LENGTH("TiDB") AS LengthOfString;
+----------------+
| LengthOfString |
+----------------+
| 4 |
+----------------+
SELECT CustomerName, CHAR_LENGTH(CustomerName) AS LengthOfName FROM Customers;
+--------------------+--------------+
| CustomerName | LengthOfName |
+--------------------+--------------+
| Albert Einstein | 15 |
| Robert Oppenheimer | 18 |
+--------------------+--------------+
注記：
上記の例は、
Customersという名前のテーブルと、テーブル内にCustomerNameという名前の列を持つデータベースが存在するという前提で動作します。
CHARACTER_LENGTH()
CHARACTER_LENGTH()関数はCHAR_LENGTH()関数と同じです。両方の関数は同じ出力を生成するため、同義語として使用できます。
CONCAT()
CONCAT()関数は、1 つ以上の引数を 1 つの文字列に連結します。
構文：
CONCAT(str1,str2,...)
str1, str2, ...は連結される引数のリストです。各引数は文字列または数値にすることができます。
例：
SELECT CONCAT('TiDB', ' ', 'Server', '-', 1, TRUE);
出力：
+---------------------------------------------+
| CONCAT('TiDB', ' ', 'Server', '-', 1, TRUE) |
+---------------------------------------------+
| TiDB Server-11 |
+---------------------------------------------+
引数のいずれかがNULL場合、 CONCAT() NULLを返します。
例：
SELECT CONCAT('TiDB', NULL, 'Server');
出力：
+--------------------------------+
| CONCAT('TiDB', NULL, 'Server') |
+--------------------------------+
| NULL |
+--------------------------------+
CONCAT()関数に加えて、次の例のように文字列を隣接させて連結することもできます。このメソッドは数値型をサポートしていないことに注意してください。
SELECT 'Ti' 'DB' ' ' 'Server';
出力：
+-------------+
| Ti |
+-------------+
| TiDB Server |
+-------------+
CONCAT_WS()
CONCAT_WS()関数は、区切り文字付きのCONCAT()の形式で、指定された区切り文字で連結された文字列を返します。
構文：
CONCAT_WS(separator,str1,str2,...)
separator: 最初の引数は区切り文字であり、NULL以外の残りの引数を連結します。str1, str2, ...: 連結される引数のリスト。各引数は文字列または数値にすることができます。
例：
SELECT CONCAT_WS(',', 'TiDB Server', 'TiKV', 'PD');
出力：
+---------------------------------------------+
| CONCAT_WS(',', 'TiDB Server', 'TiKV', 'PD') |
+---------------------------------------------+
| TiDB Server,TiKV,PD |
+---------------------------------------------+
区切り文字が空の文字列の場合、
CONCAT_WS()CONCAT()と同等となり、残りの引数の連結された文字列を返します。例：
SELECT CONCAT_WS('', 'TiDB Server', 'TiKV', 'PD');出力：
+--------------------------------------------+ | CONCAT_WS('', 'TiDB Server', 'TiKV', 'PD') | +--------------------------------------------+ | TiDB ServerTiKVPD | +--------------------------------------------+区切り文字が
NULLの場合、CONCAT_WS()NULLを返します。例：
SELECT CONCAT_WS(NULL, 'TiDB Server', 'TiKV', 'PD');出力：
+----------------------------------------------+ | CONCAT_WS(NULL, 'TiDB Server', 'TiKV', 'PD') | +----------------------------------------------+ | NULL | +----------------------------------------------+連結される引数の 1 つだけが
NULLでない場合、CONCAT_WS()その引数を返します。例：
SELECT CONCAT_WS(',', 'TiDB Server', NULL);出力：
+-------------------------------------+ | CONCAT_WS(',', 'TiDB Server', NULL) | +-------------------------------------+ | TiDB Server | +-------------------------------------+連結する引数が
NULLある場合、CONCAT_WS()これらNULLの引数をスキップします。例：
SELECT CONCAT_WS(',', 'TiDB Server', NULL, 'PD');出力：
+-------------------------------------------+ | CONCAT_WS(',', 'TiDB Server', NULL, 'PD') | +-------------------------------------------+ | TiDB Server,PD | +-------------------------------------------+連結する空の文字列がある場合、
CONCAT_WS()空の文字列をスキップしません。例：
SELECT CONCAT_WS(',', 'TiDB Server', '', 'PD');出力：
+-----------------------------------------+ | CONCAT_WS(',', 'TiDB Server', '', 'PD') | +-----------------------------------------+ | TiDB Server,,PD | +-----------------------------------------+
ELT()
ELT()関数はインデックス番号の要素を返します。
SELECT ELT(3, 'This', 'is', 'TiDB');
+------------------------------+
| ELT(3, 'This', 'is', 'TiDB') |
+------------------------------+
| TiDB |
+------------------------------+
1 row in set (0.00 sec)
上記の例では、3 番目の要素である'TiDB'が返されます。
EXPORT_SET()
EXPORT_SET()関数は、指定された数 ( number_of_bits ) のon / off個の値 (オプションでseparatorで区切られる) で構成される文字列を返します。これらの値は、 bits引数の対応するビットが1であるかどうかに基づいており、最初の値はbitsの右端 (最下位) ビットに対応します。
構文：
EXPORT_SET(bits, on, off, [separator[, number_of_bits]])
bits: ビット値を表す整数。on: 対応するビットが1の場合に返される文字列。off: 対応するビットが0の場合に返される文字列。separator(オプション): 結果文字列の区切り文字。number_of_bits(オプション): 処理するビット数。設定されていない場合は、デフォルトで64(ビットの最大サイズ) が使用され、bitsは符号なし 64 ビット整数として扱われます。
例:
次の例では、 number_of_bitsが5に設定され、 |で区切られた 5 つの値が生成されます。 3 ビットのみが指定されているため、他のビットは設定されていないと見なされます。 したがって、 number_of_bits 101または00101に設定すると、出力は同じになります。
SELECT EXPORT_SET(b'101',"ON",'off','|',5);
+-------------------------------------+
| EXPORT_SET(b'101',"ON",'off','|',5) |
+-------------------------------------+
| ON|off|ON|off|off |
+-------------------------------------+
1 row in set (0.00 sec)
次の例では、 bitsは00001111に、 onはxに、 offは_に設定されています。これにより、関数は0ビットに対して____を返し、 1ビットに対してxxxx返します。したがって、 00001111のビットを右から左に処理すると、関数はxxxx____を返します。
SELECT EXPORT_SET(b'00001111', 'x', '_', '', 8);
+------------------------------------------+
| EXPORT_SET(b'00001111', 'x', '_', '', 8) |
+------------------------------------------+
| xxxx____ |
+------------------------------------------+
1 row in set (0.00 sec)
次の例では、 bitsは00001111に、 onはxに、 offは_に設定されています。これにより、関数は1ビットごとにxを返し、 0ビットごとに_返します。したがって、 01010101のビットを右から左に処理すると、関数はx_x_x_x_を返します。
SELECT EXPORT_SET(b'01010101', 'x', '_', '', 8);
+------------------------------------------+
| EXPORT_SET(b'01010101', 'x', '_', '', 8) |
+------------------------------------------+
| x_x_x_x_ |
+------------------------------------------+
1 row in set (0.00 sec)
FIELD()
後続の引数の最初の引数のインデックス (位置) を返します。
次の例では、 FIELD()の最初の引数はneedleであり、次のリストの 2 番目の引数と一致するため、関数は2返します。
SELECT FIELD('needle', 'A', 'needle', 'in', 'a', 'haystack');
+-------------------------------------------------------+
| FIELD('needle', 'A', 'needle', 'in', 'a', 'haystack') |
+-------------------------------------------------------+
| 2 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
FIND_IN_SET()
2 番目の引数内の最初の引数のインデックス位置を返します。
この関数は、 SETデータ型でよく使用されます。
次の例では、 GoセットCOBOL,BASIC,Rust,Go,Java,Fortranの 4 番目の要素なので、関数は4返します。
SELECT FIND_IN_SET('Go', 'COBOL,BASIC,Rust,Go,Java,Fortran');
+-------------------------------------------------------+
| FIND_IN_SET('Go', 'COBOL,BASIC,Rust,Go,Java,Fortran') |
+-------------------------------------------------------+
| 4 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
FORMAT()
FORMAT(X,D[,locale])関数は、数値X "#,###,###. ##"と同様の形式にフォーマットし、小数点以下D桁に丸めて、結果を文字列として返すために使用されます。
引数:
X: 書式設定する数値。直接の数値、数値文字列、または科学的記数法の数値にすることができます。D: 返される値の小数点以下の桁数。関数は数値Xを小数点以下のD桁に丸めます。DXの実際の小数点以下の桁数より大きい場合、結果は対応する長さまでゼロで埋められます。[locale]: 小数点、千単位の区切り、および結果の数値の区切りのグループ化に使用するロケール設定を指定します。有効なロケール値は、lc_time_namesシステム変数の有効な値と同じです。指定されていない場合、または地域設定がNULLの場合、デフォルトで'en_US'地域設定が使用されます。この引数はオプションです。
動作:
- 最初の引数が文字列で、数字のみが含まれている場合、関数はその数値に基づいて結果を返します。たとえば、
FORMAT('12.34', 1)とFORMAT(12.34, 1)同じ結果を返します。 - 最初の引数が科学的記数法（
E/eを使用）で表される数値の場合、関数はその数値に基づいて結果を返します。たとえば、FORMAT('1E2', 3)100.000を返します。 - 最初の引数が数字以外の文字で始まる文字列の場合、関数は 0 と警告
(Code 1292)返します。たとえば、FORMAT('q12.36', 5)0.00000を返しますが、警告Warning (Code 1292): Truncated incorrect DOUBLE value: 'q12.36'も含まれます。 - 最初の引数が数値と非数値が混在する文字列の場合、関数は引数の先頭の連続する数値部分に基づいて結果を返し、警告
(Code 1292)も含まれます。たとえば、FORMAT('12.36q56.78', 1)FORMAT('12.36', 1)と同じ数値結果を返しますが、警告Warning (Code 1292): Truncated incorrect DOUBLE value: '12.36q56.78'が含まれます。 - 2 番目の引数が 0 または負の数の場合、関数は小数部分を切り捨てて整数を返します。
- 引数のいずれかが
NULL場合、関数はNULLを返します。
例:
次の例は、数値 12.36 をさまざまな小数点以下の桁にフォーマットする方法を示しています。
mysql> SELECT FORMAT(12.36, 1);
+------------------+
| FORMAT(12.36, 1) |
+------------------+
| 12.4 |
+------------------+
mysql> SELECT FORMAT(12.36, 5);
+------------------+
| FORMAT(12.36, 5) |
+------------------+
| 12.36000 |
+------------------+
mysql> SELECT FORMAT(12.36, 2);
+------------------+
| FORMAT(12.36, 2) |
+------------------+
| 12.36 |
+------------------+
FROM_BASE64()
FROM_BASE64()関数は、 ベース64でエンコードされた文字列をデコードし、デコードされた結果を 16 進形式で返すために使用されます。
- この関数は、デコードする Base64 でエンコードされた文字列という単一の引数を受け入れます。
- 引数が
NULLであるか、有効な Base64 エンコードされた文字列でない場合、FROM_BASE64()関数はNULLを返します。
例:
次の例は、Base64 でエンコードされた文字列'SGVsbG8gVGlEQg=='をデコードする方法を示しています。この文字列は、 TO_BASE64()関数を使用して'Hello TiDB'をエンコードした結果です。
mysql> SELECT TO_BASE64('Hello TiDB');
+-------------------------+
| TO_BASE64('Hello TiDB') |
+-------------------------+
| SGVsbG8gVGlEQg== |
+-------------------------+
mysql> SELECT FROM_BASE64('SGVsbG8gVGlEQg==');
+------------------------------------------------------------------+
| FROM_BASE64('SGVsbG8gVGlEQg==') |
+------------------------------------------------------------------+
| 0x48656C6C6F2054694442 |
+------------------------------------------------------------------+
mysql> SELECT CONVERT(FROM_BASE64('SGVsbG8gVGlEQg==') USING utf8mb4);
+--------------------------------------------------------+
| CONVERT(FROM_BASE64('SGVsbG8gVGlEQg==') USING utf8mb4) |
+--------------------------------------------------------+
| Hello TiDB |
+--------------------------------------------------------+
次の例は、Base64 でエンコードされた数値MTIzNDU2をデコードする方法を示しています。この文字列は、 TO_BASE64()関数を使用して実行できる123456のエンコードの結果です。
mysql> SELECT FROM_BASE64('MTIzNDU2');
+--------------------------------------------------+
| FROM_BASE64('MTIzNDU2') |
+--------------------------------------------------+
| 0x313233343536 |
+--------------------------------------------------+
HEX()
HEX()関数は、指定された引数をその 16 進数値の文字列表現に変換するために使用されます。引数は文字列または数値のいずれかになります。
- 引数が文字列の場合、
HEX(str)strの 16 進文字列表現を返します。関数は、strの各文字の各バイトを 2 つの 16 進数字に変換します。たとえば、UTF-8 または ASCII 文字セットの文字a、バイナリ値00111101、つまり 16 進表記では61として表されます。 - 引数が数値の場合、
HEX(n)nの 16 進文字列表現を返します。この関数は引数nBIGINT数値として扱い、CONV(n, 10, 16)を使用する場合と同等になります。 - 引数が
NULLの場合、関数はNULL返します。
例:
SELECT X'616263', HEX('abc'), UNHEX(HEX('abc')), 0x616263;
+-----------+------------+-------------------+----------+
| X'616263' | HEX('abc') | UNHEX(HEX('abc')) | 0x616263 |
+-----------+------------+-------------------+----------+
| abc | 616263 | abc | abc |
+-----------+------------+-------------------+----------+
SELECT X'F09F8DA3', HEX('🍣'), UNHEX(HEX('🍣')), 0xF09F8DA3;
+-------------+-------------+--------------------+------------+
| X'F09F8DA3' | HEX('🍣') | UNHEX(HEX('🍣')) | 0xF09F8DA3 |
+-------------+-------------+--------------------+------------+
| 🍣 | F09F8DA3 | 🍣 | 🍣 |
+-------------+-------------+--------------------+------------+
SELECT HEX(255), CONV(HEX(255), 16, 10);
+----------+------------------------+
| HEX(255) | CONV(HEX(255), 16, 10) |
+----------+------------------------+
| FF | 255 |
+----------+------------------------+
SELECT HEX(NULL);
+-----------+
| HEX(NULL) |
+-----------+
| NULL |
+-----------+
INSERT()
INSERT(str, pos, len, newstr)関数は、 str内の部分文字列 (位置posから始まり、長さがlen文字) を文字列newstrに置き換えるために使用されます。この関数はマルチバイトセーフです。
posstrの長さを超える場合、関数は変更せずに元の文字列strを返します。len位置posからの残りの長さstrを超える場合、関数は位置posからの残りの文字列を置き換えます。- いずれかの引数が
NULL場合、関数はNULLを返します。
例:
SELECT INSERT('He likes tennis', 4, 5, 'plays');
+------------------------------------------+
| INSERT('He likes tennis', 4, 5, 'plays') |
+------------------------------------------+
| He plays tennis |
+------------------------------------------+
SELECT INSERT('He likes tennis', -1, 5, 'plays');
+-------------------------------------------+
| INSERT('He likes tennis', -1, 5, 'plays') |
+-------------------------------------------+
| He likes tennis |
+-------------------------------------------+
SELECT INSERT('He likes tennis', 4, 100, 'plays');
+--------------------------------------------+
| INSERT('He likes tennis', 4, 100, 'plays') |
+--------------------------------------------+
| He plays |
+--------------------------------------------+
SELECT INSERT('He likes tenis', 10, 100, '🍣');
+-------------------------------------------+
| INSERT('He likes tenis', 10, 100, '🍣') |
+-------------------------------------------+
| He likes 🍣 |
+-------------------------------------------+
SELECT INSERT('あああああああ', 2, 3, 'いいい');
+----------------------------------------------------+
| INSERT('あああああああ', 2, 3, 'いいい') |
+----------------------------------------------------+
| あいいいあああ |
+----------------------------------------------------+
SELECT INSERT('あああああああ', 2, 3, 'xx');
+---------------------------------------------+
| INSERT('あああああああ', 2, 3, 'xx') |
+---------------------------------------------+
| あxxあああ |
+---------------------------------------------+
INSTR()
INSTR(str, substr)関数は、 str内でsubstrが最初に出現する位置を取得するために使用されます。各引数は文字列または数値のいずれかになります。この関数は、引数が 2 つのバージョンのLOCATE(substr, str)と同じですが、引数の順序が逆になっています。
注記：
INSTR(str, substr)の大文字と小文字の区別は、TiDB で使用される照合順序によって決まります。バイナリ照合順序 (サフィックス_bin付き) では大文字と小文字が区別されますが、一般照合順序 (サフィックス_general_ciまたは_ai_ci付き) では大文字と小文字は区別されません。
- いずれかの引数が数値の場合、関数はその数値を文字列として扱います。
substrstrにない場合、関数は0返します。そうでない場合は、str内でsubstrが最初に出現する位置を返します。- いずれかの引数が
NULL場合、関数はNULLを返します。
例:
SELECT INSTR("pingcap.com", "tidb");
+------------------------------+
| INSTR("pingcap.com", "tidb") |
+------------------------------+
| 0 |
+------------------------------+
SELECT INSTR("pingcap.com/tidb", "tidb");
+-----------------------------------+
| INSTR("pingcap.com/tidb", "tidb") |
+-----------------------------------+
| 13 |
+-----------------------------------+
SELECT INSTR("pingcap.com/tidb" COLLATE utf8mb4_bin, "TiDB");
+-------------------------------------------------------+
| INSTR("pingcap.com/tidb" COLLATE utf8mb4_bin, "TiDB") |
+-------------------------------------------------------+
| 0 |
+-------------------------------------------------------+
SELECT INSTR("pingcap.com/tidb" COLLATE utf8mb4_general_ci, "TiDB");
+--------------------------------------------------------------+
| INSTR("pingcap.com/tidb" COLLATE utf8mb4_general_ci, "TiDB") |
+--------------------------------------------------------------+
| 13 |
+--------------------------------------------------------------+
SELECT INSTR(0123, "12");
+-------------------+
| INSTR(0123, "12") |
+-------------------+
| 1 |
+-------------------+
LCASE()
LCASE(str)関数はLOWER(str)の同義語で、指定された引数の小文字を返します。
LEFT()
LEFT()関数は、文字列の左側から指定された数の文字を返します。
構文：
LEFT(`str`, `len`)
str: 文字を抽出する元の文字列。2strマルチバイト文字が含まれている場合、関数はそれを単一のコード ポイントとしてカウントします。len: 返される文字の長さ。len0 以下の場合、関数は空の文字列を返します。lenstrの長さ以上である場合、関数は元のstrを返します。
- いずれかの引数が
NULL場合、関数はNULLを返します。
例:
SELECT LEFT('ABCED', 3);
+------------------+
| LEFT('ABCED', 3) |
+------------------+
| ABC |
+------------------+
SELECT LEFT('ABCED', 6);
+------------------+
| LEFT('ABCED', 6) |
+------------------+
| ABCED |
+------------------+
SELECT LEFT('ABCED', 0);
+------------------+
| LEFT('ABCED', 0) |
+------------------+
| |
+------------------+
SELECT LEFT('ABCED', -1);
+-------------------+
| LEFT('ABCED', -1) |
+-------------------+
| |
+-------------------+
SELECT LEFT('🍣ABC', 3);
+--------------------+
| LEFT('🍣ABC', 3) |
+--------------------+
| 🍣AB |
+--------------------+
SELECT LEFT('ABC', NULL);
+-------------------+
| LEFT('ABC', NULL) |
+-------------------+
| NULL |
+-------------------+
SELECT LEFT(NULL, 3);
+------------------------------+
| LEFT(NULL, 3) |
+------------------------------+
| NULL |
+------------------------------+
LENGTH()
LENGTH()関数は文字列の長さをバイト単位で返します。
LENGTH()マルチバイト文字を複数のバイトとしてカウントし、 CHAR_LENGTH()マルチバイト文字を単一のコード ポイントとしてカウントします。
引数がNULLの場合、関数はNULL返します。
例:
SELECT LENGTH('ABC');
+---------------+
| LENGTH('ABC') |
+---------------+
| 3 |
+---------------+
SELECT LENGTH('🍣ABC');
+-------------------+
| LENGTH('🍣ABC') |
+-------------------+
| 7 |
+-------------------+
SELECT CHAR_LENGTH('🍣ABC');
+------------------------+
| CHAR_LENGTH('🍣ABC') |
+------------------------+
| 4 |
+------------------------+
SELECT LENGTH(NULL);
+--------------+
| LENGTH(NULL) |
+--------------+
| NULL |
+--------------+
LIKE
LIKE演算子は単純な文字列マッチングに使用されます。式expr LIKE pat [ESCAPE 'escape_char']は1 ( TRUE ) または0 ( FALSE ) を返します。13 またはexpr patいずれかがNULLの場合、結果はNULLになります。
LIKEでは次の 2 つのワイルドカード パラメータを使用できます。
%、ゼロ文字を含む任意の数の文字に一致します。_1 文字のみに一致します。
次の例では、 utf8mb4_bin照合順序を使用しています。
SET collation_connection='utf8mb4_bin';
SHOW VARIABLES LIKE 'collation_connection';
+----------------------+-------------+
| Variable_name | Value |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
+----------------------+-------------+
SELECT NULL LIKE '%' as result;
+--------+
| result |
+--------+
| NULL |
+--------+
SELECT 'sushi!!!' LIKE 'sushi_' AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
SELECT '🍣🍺sushi🍣🍺' LIKE '%sushi%' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
SELECT '🍣🍺sushi🍣🍺' LIKE '%SUSHI%' AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
SELECT '🍣🍺sushi🍣🍺' LIKE '%🍣%' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
デフォルトのエスケープ文字は\です。
SELECT 'sushi!!!' LIKE 'sushi\_' AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
SELECT 'sushi_' LIKE 'sushi\_' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
*などの別のエスケープ文字を指定するには、 ESCAPE句を使用します。
SELECT 'sushi_' LIKE 'sushi*_' ESCAPE '*' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
SELECT 'sushi!' LIKE 'sushi*_' ESCAPE '*' AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
LIKE演算子を使用して数値を一致させることができます。
SELECT 10 LIKE '1%' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
SELECT 10000 LIKE '12%' AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
utf8mb4_unicode_ciなどの照合順序を明示的に指定するには、 COLLATE使用します。
SELECT '🍣🍺Sushi🍣🍺' COLLATE utf8mb4_unicode_ci LIKE '%SUSHI%' AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
LOCATE()
LOCATE(substr, str[, pos])関数は、文字列str内の指定された部分文字列substrの最初の出現位置を取得するために使用されます。 pos引数はオプションであり、検索の開始位置を指定します。
- 部分文字列
substrがstrに存在しない場合、関数は0返します。 - いずれかの引数が
NULL場合、関数はNULLを返します。 - この関数はマルチバイトセーフであり、少なくとも 1 つの引数がバイナリ文字列である場合にのみ大文字と小文字を区別した検索を実行します。
次の例では、 utf8mb4_bin照合順序を使用しています。
SET collation_connection='utf8mb4_bin';
SHOW VARIABLES LIKE 'collation_connection';
+----------------------+-------------+
| Variable_name | Value |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
+----------------------+-------------+
SELECT LOCATE('bar', 'foobarbar');
+----------------------------+
| LOCATE('bar', 'foobarbar') |
+----------------------------+
| 4 |
+----------------------------+
SELECT LOCATE('baz', 'foobarbar');
+----------------------------+
| LOCATE('baz', 'foobarbar') |
+----------------------------+
| 0 |
+----------------------------+
SELECT LOCATE('bar', 'fooBARBAR');
+----------------------------+
| LOCATE('bar', 'fooBARBAR') |
+----------------------------+
| 0 |
+----------------------------+
SELECT LOCATE('bar', 'foobarBAR', 100);
+---------------------------------+
| LOCATE('bar', 'foobarBAR', 100) |
+---------------------------------+
| 0 |
+---------------------------------+
SELECT LOCATE('bar', 'foobarbar', 5);
+-------------------------------+
| LOCATE('bar', 'foobarbar', 5) |
+-------------------------------+
| 7 |
+-------------------------------+
SELECT LOCATE('bar', NULL);
+---------------------+
| LOCATE('bar', NULL) |
+---------------------+
| NULL |
+---------------------+
SELECT LOCATE('い', 'たいでぃーびー');
+----------------------------------------+
| LOCATE('い', 'たいでぃーびー') |
+----------------------------------------+
| 2 |
+----------------------------------------+
SELECT LOCATE('い', 'たいでぃーびー', 3);
+-------------------------------------------+
| LOCATE('い', 'たいでぃーびー', 3) |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
次の例では、 utf8mb4_unicode_ci照合順序を使用しています。
SET collation_connection='utf8mb4_unicode_ci';
SHOW VARIABLES LIKE 'collation_connection';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
+----------------------+--------------------+
SELECT LOCATE('い', 'たいでぃーびー', 3);
+-------------------------------------------+
| LOCATE('い', 'たいでぃーびー', 3) |
+-------------------------------------------+
| 4 |
+-------------------------------------------+
SELECT LOCATE('🍺', '🍣🍣🍣🍺🍺');
+----------------------------------------+
| LOCATE('🍺', '🍣🍣🍣🍺🍺') |
+----------------------------------------+
| 1 |
+----------------------------------------+
次のマルチバイト文字列とバイナリ文字列の例では、 utf8mb4_bin照合順序順序が使用されています。
SET collation_connection='utf8mb4_bin';
SHOW VARIABLES LIKE 'collation_connection';
+----------------------+-------------+
| Variable_name | Value |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
+----------------------+-------------+
SELECT LOCATE('🍺', '🍣🍣🍣🍺🍺');
+----------------------------------------+
| LOCATE('🍺', '🍣🍣🍣🍺🍺') |
+----------------------------------------+
| 4 |
+----------------------------------------+
SELECT LOCATE('b', _binary'aBcde');
+-----------------------------+
| LOCATE('b', _binary'aBcde') |
+-----------------------------+
| 0 |
+-----------------------------+
SELECT LOCATE('B', _binary'aBcde');
+-----------------------------+
| LOCATE('B', _binary'aBcde') |
+-----------------------------+
| 2 |
+-----------------------------+
SELECT LOCATE(_binary'b', 'aBcde');
+-----------------------------+
| LOCATE(_binary'b', 'aBcde') |
+-----------------------------+
| 0 |
+-----------------------------+
SELECT LOCATE(_binary'B', 'aBcde');
+-----------------------------+
| LOCATE(_binary'B', 'aBcde') |
+-----------------------------+
| 2 |
+-----------------------------+
LOWER()
LOWER(str)関数は、指定された引数str内のすべての文字を小文字に変換するために使用されます。引数は文字列または数値のいずれかになります。
- 引数が文字列の場合、関数は小文字で文字列を返します。
- 引数が数値の場合、関数は先頭のゼロを除いた数値を返します。
- 引数が
NULLの場合、関数はNULL返します。
例:
SELECT LOWER("TiDB");
+---------------+
| LOWER("TiDB") |
+---------------+
| tidb |
+---------------+
SELECT LOWER(-012);
+-------------+
| LOWER(-012) |
+-------------+
| -12 |
+-------------+
LPAD()
LPAD(str, len, padstr)関数は、指定された文字列padstrを左側に埋め込んでlen文字の長さにした文字列引数を返します。
len文字列strの長さより短い場合、関数は文字列strをlenの長さに切り捨てます。len負の数の場合、関数はNULLを返します。- いずれかの引数が
NULL場合、関数はNULLを返します。
例:
SELECT LPAD('TiDB',8,'>');
+--------------------+
| LPAD('TiDB',8,'>') |
+--------------------+
| >>>>TiDB |
+--------------------+
1 row in set (0.00 sec)
SELECT LPAD('TiDB',2,'>');
+--------------------+
| LPAD('TiDB',2,'>') |
+--------------------+
| Ti |
+--------------------+
1 row in set (0.00 sec)
SELECT LPAD('TiDB',-2,'>');
+---------------------+
| LPAD('TiDB',-2,'>') |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.00 sec)
LTRIM()
LTRIM()関数は、指定された文字列の先頭のスペースを削除します。
引数がNULLの場合、この関数はNULL返します。
注記：
この関数は、スペース文字 (U+0020) のみを削除し、タブ (U+0009) や改行なしスペース (U+00A0) などの他のスペースのような文字は削除しません。
例:
次の例では、 LTRIM()関数は' hello'から先頭のスペースを削除し、 helloを返します。
SELECT LTRIM(' hello');
+--------------------+
| LTRIM(' hello') |
+--------------------+
| hello |
+--------------------+
1 row in set (0.00 sec)
次の例では、 CONCAT()使用して、 LTRIM(' hello')の結果を«と»で囲んでいます。この書式設定により、先頭のスペースがすべて削除されていることが少しわかりやすくなります。
SELECT CONCAT('«',LTRIM(' hello'),'»');
+------------------------------------+
| CONCAT('«',LTRIM(' hello'),'»') |
+------------------------------------+
| «hello» |
+------------------------------------+
1 row in set (0.00 sec)
MAKE_SET()
MAKE_SET()関数は、 bits引数の対応するビットが1に設定されているかどうかに基づいて、コンマで区切られた文字列のセットを返します。
構文：
MAKE_SET(bits, str1, str2, ...)
bits: 結果セットに含める後続の文字列引数を制御します。bitsがNULLに設定されている場合、関数はNULLを返します。str1, str2, ...: 文字列のリスト。各文字列は、右から左へのbits引数のビットに対応します。str1右から最初のビットに対応し、str2右から 2 番目のビットに対応します。以下同様です。対応するビットが1の場合、文字列は結果に含まれます。それ以外の場合は含まれません。
例:
次の例では、引数bitsのすべてのビットが0に設定されているため、関数は結果に後続の文字列を含めず、空の文字列を返します。
SELECT MAKE_SET(b'000','foo','bar','baz');
+------------------------------------+
| MAKE_SET(b'000','foo','bar','baz') |
+------------------------------------+
| |
+------------------------------------+
1 row in set (0.00 sec)
次の例では、右から最初のビットのみが1であるため、関数は最初の文字列fooのみを返します。
SELECT MAKE_SET(b'001','foo','bar','baz');
+------------------------------------+
| MAKE_SET(b'001','foo','bar','baz') |
+------------------------------------+
| foo |
+------------------------------------+
1 row in set (0.00 sec)
次の例では、右から 2 番目のビットのみが1であるため、関数は 2 番目の文字列barのみを返します。
SELECT MAKE_SET(b'010','foo','bar','baz');
+------------------------------------+
| MAKE_SET(b'010','foo','bar','baz') |
+------------------------------------+
| bar |
+------------------------------------+
1 row in set (0.00 sec)
次の例では、右から 3 番目のビットのみが1であるため、関数は 3 番目の文字列bazのみを返します。
SELECT MAKE_SET(b'100','foo','bar','baz');
+------------------------------------+
| MAKE_SET(b'100','foo','bar','baz') |
+------------------------------------+
| baz |
+------------------------------------+
1 row in set (0.00 sec)
次の例では、すべてのビットが1あるため、関数は 3 つの文字列すべてをコンマ区切りの結果セットで返します。
SELECT MAKE_SET(b'111','foo','bar','baz');
+------------------------------------+
| MAKE_SET(b'111','foo','bar','baz') |
+------------------------------------+
| foo,bar,baz |
+------------------------------------+
1 row in set (0.0002 sec)
MID()
MID(str, pos, len)関数は、指定されたpos位置から始まりlen長さの部分文字列を返します。
引数のいずれかがNULL場合、関数はNULLを返します。
TiDB はこの関数の 2 つの引数のバリアントをサポートしていません。詳細については、 ＃52420参照してください。
例:
次の例では、 MID() 、入力文字列の 2 番目の文字 ( b ) から始まる3文字の長さの部分文字列を返します。
SELECT MID('abcdef',2,3);
+-------------------+
| MID('abcdef',2,3) |
+-------------------+
| bcd |
+-------------------+
1 row in set (0.00 sec)
NOT LIKE
単純なパターンマッチングの否定。
この関数はLIKEの逆演算を実行します。
例:
次の例では、 aaaがa%パターンと一致するため、 NOT LIKE 0 (False) を返します。
SELECT 'aaa' LIKE 'a%', 'aaa' NOT LIKE 'a%';
+-----------------+---------------------+
| 'aaa' LIKE 'a%' | 'aaa' NOT LIKE 'a%' |
+-----------------+---------------------+
| 1 | 0 |
+-----------------+---------------------+
1 row in set (0.00 sec)
次の例では、 aaa b%パターンと一致しないため、 NOT LIKE 1 (True) を返します。
SELECT 'aaa' LIKE 'b%', 'aaa' NOT LIKE 'b%';
+-----------------+---------------------+
| 'aaa' LIKE 'b%' | 'aaa' NOT LIKE 'b%' |
+-----------------+---------------------+
| 0 | 1 |
+-----------------+---------------------+
1 row in set (0.00 sec)
NOT REGEXP
REGEXPの否定。
OCT()
数値の8進数 (基数 8) 表現を含む文字列を返します。
例:
次の例では、 再帰共通テーブル式 (CTE)を使用して 0 から 20 までの数値のシーケンスを生成し、 OCT()関数を使用して各数値を 8 進数表現に変換します。0 から 7 までの 10 進数値は、8 進数でも同じ表現になります。8 から 15 までの 10 進数値は、10 から 17 までの 8 進数値に対応します。
WITH RECURSIVE nr(n) AS (
SELECT 0 AS n
UNION ALL
SELECT n+1 FROM nr WHERE n<20
)
SELECT n, OCT(n) FROM nr;
+------+--------+
| n | OCT(n) |
+------+--------+
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 10 |
| 9 | 11 |
| 10 | 12 |
| 11 | 13 |
| 12 | 14 |
| 13 | 15 |
| 14 | 16 |
| 15 | 17 |
| 16 | 20 |
| 17 | 21 |
| 18 | 22 |
| 19 | 23 |
| 20 | 24 |
+------+--------+
20 rows in set (0.00 sec)
OCTET_LENGTH()
LENGTH()の同義語。
ORD()
指定された引数の左端の文字の文字コードを返します。
この関数はCHAR()と似ていますが、逆の動作をします。
例:
aとAを例にとると、 ORD() aに対して97返し、 Aに対して65返します。
SELECT ORD('a'), ORD('A');
+----------+----------+
| ORD('a') | ORD('A') |
+----------+----------+
| 97 | 65 |
+----------+----------+
1 row in set (0.00 sec)
ORD()から取得した文字コードを入力として受け取ると、 CHAR()関数を使用して元の文字を戻すことができます。出力形式は、MySQL クライアントでbinary-as-hexオプションが有効になっているかどうかによって異なる場合があることに注意してください。
SELECT CHAR(97), CHAR(65);
+----------+----------+
| CHAR(97) | CHAR(65) |
+----------+----------+
| a | A |
+----------+----------+
1 row in set (0.01 sec)
次の例は、 ORD()マルチバイト文字を処理する方法を示しています。ここでは、 101と0x65はどちらもe文字目の UTF-8 エンコード値ですが、形式が異なります。また、 50091と0xC3ABはどちらもë文字目の同じ値を表します。
SELECT ORD('e'), ORD('ë'), HEX('e'), HEX('ë');
+----------+-----------+----------+-----------+
| ORD('e') | ORD('ë') | HEX('e') | HEX('ë') |
+----------+-----------+----------+-----------+
| 101 | 50091 | 65 | C3AB |
+----------+-----------+----------+-----------+
1 row in set (0.00 sec)
POSITION()
LOCATE()の同義語。
QUOTE()
SQL ステートメントで使用するために引数をエスケープします。
引数がNULLの場合、関数はNULL返します。
例：
16 進数でエンコードされた値を表示する代わりに結果を直接表示するには、MySQL クライアントを--skip-binary-as-hexオプションで起動する必要があります。
次の例では、ASCII NULL 文字が\0としてエスケープされ、一重引用符文字'が\'としてエスケープされていることを示しています。
SELECT QUOTE(0x002774657374);
+-----------------------+
| QUOTE(0x002774657374) |
+-----------------------+
| '\0\'test' |
+-----------------------+
1 row in set (0.00 sec)
REGEXP
正規表現を使用したパターンマッチング。
例:
この例では、いくつかの文字列が 2 つの正規表現と照合されます。
WITH vals AS (
SELECT 'TiDB' AS v
UNION ALL
SELECT 'Titanium'
UNION ALL
SELECT 'Tungsten'
UNION ALL
SELECT 'Rust'
)
SELECT
v,
v REGEXP '^Ti' AS 'starts with "Ti"',
v REGEXP '^.{4}$' AS 'Length is 4 characters'
FROM
vals;
+----------+------------------+------------------------+
| v | starts with "Ti" | Length is 4 characters |
+----------+------------------+------------------------+
| TiDB | 1 | 1 |
| Titanium | 1 | 0 |
| Tungsten | 0 | 0 |
| Rust | 0 | 1 |
+----------+------------------+------------------------+
4 rows in set (0.00 sec)
次の例は、 REGEXP SELECT節に限定されないことを示しています。たとえば、クエリのWHERE節でも使用できます。
SELECT
v
FROM (
SELECT 'TiDB' AS v
) AS vals
WHERE
v REGEXP 'DB$';
+------+
| v |
+------+
| TiDB |
+------+
1 row in set (0.01 sec)
REGEXP_INSTR()
正規表現に一致する部分文字列の開始インデックスを返します（MySQLと部分的に互換性があります。詳細についてはMySQL との正規表現の互換性を参照してください）。
REGEXP_INSTR(str, regexp, [start, [match, [ret, [match_type]]]])関数は正規表現（ regexp ）が文字列（ str ）と一致する場合、一致した位置を返します。
strまたはregexpいずれかがNULLの場合、関数はNULLを返します。
例:
以下の例では、 ^.b.$ abcと一致していることがわかります。
SELECT REGEXP_INSTR('abc','^.b.$');
+-----------------------------+
| REGEXP_INSTR('abc','^.b.$') |
+-----------------------------+
| 1 |
+-----------------------------+
1 row in set (0.00 sec)
次の例では、3 番目の引数を使用して、文字列内の異なる開始位置との一致を検索します。
SELECT REGEXP_INSTR('abcabc','a');
+----------------------------+
| REGEXP_INSTR('abcabc','a') |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)
SELECT REGEXP_INSTR('abcabc','a',2);
+------------------------------+
| REGEXP_INSTR('abcabc','a',2) |
+------------------------------+
| 4 |
+------------------------------+
1 row in set (0.00 sec)
次の例では、4 番目の引数を使用して 2 番目の一致を検索します。
SELECT REGEXP_INSTR('abcabc','a',1,2);
+--------------------------------+
| REGEXP_INSTR('abcabc','a',1,2) |
+--------------------------------+
| 4 |
+--------------------------------+
1 row in set (0.00 sec)
次の例では、5 番目の引数を使用して、一致の値ではなく、一致後の値を返します。
SELECT REGEXP_INSTR('abcabc','a',1,1,1);
+----------------------------------+
| REGEXP_INSTR('abcabc','a',1,1,1) |
+----------------------------------+
| 2 |
+----------------------------------+
1 row in set (0.00 sec)
次の例では、6 番目の引数を使用してiフラグを追加し、大文字と小文字を区別しない一致を取得します。正規表現match_typeの詳細については、 match_type互換性を参照してください。
SELECT REGEXP_INSTR('abcabc','A',1,1,0,'');
+-------------------------------------+
| REGEXP_INSTR('abcabc','A',1,1,0,'') |
+-------------------------------------+
| 0 |
+-------------------------------------+
1 row in set (0.00 sec)
SELECT REGEXP_INSTR('abcabc','A',1,1,0,'i');
+--------------------------------------+
| REGEXP_INSTR('abcabc','A',1,1,0,'i') |
+--------------------------------------+
| 1 |
+--------------------------------------+
1 row in set (0.00 sec)
match_type他に、 照合順序もマッチングに影響します。次の例では、大文字と小文字を区別する照合と大文字と小文字を区別しない照合順序を使用してこれを示します。
SELECT REGEXP_INSTR('abcabc','A' COLLATE utf8mb4_general_ci);
+-------------------------------------------------------+
| REGEXP_INSTR('abcabc','A' COLLATE utf8mb4_general_ci) |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+
1 row in set (0.01 sec)
SELECT REGEXP_INSTR('abcabc','A' COLLATE utf8mb4_bin);
+------------------------------------------------+
| REGEXP_INSTR('abcabc','A' COLLATE utf8mb4_bin) |
+------------------------------------------------+
| 0 |
+------------------------------------------------+
1 row in set (0.00 sec)
REGEXP_LIKE()
文字列が正規表現に一致するかどうか（MySQLと部分的に互換性があります。詳細についてはMySQL との正規表現の互換性を参照してください）。
REGEXP_LIKE(str, regex, [match_type])関数は、正規表現が文字列と一致するかどうかをテストするために使用されます。オプションで、 match_typeを使用して一致動作を変更できます。
例:
次の例は、 ^a abcと一致することを示しています。
SELECT REGEXP_LIKE('abc','^a');
+-------------------------+
| REGEXP_LIKE('abc','^a') |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)
次の例は、 ^A abcと一致しないことを示しています。
SELECT REGEXP_LIKE('abc','^A');
+-------------------------+
| REGEXP_LIKE('abc','^A') |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)
この例では、 ^A abcに一致しますが、これは、大文字と小文字を区別しない一致を有効にするiフラグによって一致するようになりました。正規表現match_typeの詳細については、 match_type互換性を参照してください。
SELECT REGEXP_LIKE('abc','^A','i');
+-----------------------------+
| REGEXP_LIKE('abc','^A','i') |
+-----------------------------+
| 1 |
+-----------------------------+
1 row in set (0.00 sec)
REGEXP_REPLACE()
正規表現に一致する部分文字列を置き換えます（MySQLと部分的に互換性があります。詳細についてはMySQL との正規表現の互換性を参照してください）。
REGEXP_REPLACE(str, regexp, replace, [start, [match, [match_type]]])関数は、正規表現に基づいて文字列を置換するために使用できます。
例:
次の例では、 2 つの o がiに置き換えられます。
SELECT REGEXP_REPLACE('TooDB', 'o{2}', 'i');
+--------------------------------------+
| REGEXP_REPLACE('TooDB', 'o{2}', 'i') |
+--------------------------------------+
| TiDB |
+--------------------------------------+
1 row in set (0.00 sec)
次の例では、3 番目の文字から一致が開始され、正規表現は一致せず、置換も行われません。
SELECT REGEXP_REPLACE('TooDB', 'o{2}', 'i',3);
+----------------------------------------+
| REGEXP_REPLACE('TooDB', 'o{2}', 'i',3) |
+----------------------------------------+
| TooDB |
+----------------------------------------+
1 row in set (0.00 sec)
次の例では、5 番目の引数を使用して、置換に最初の一致を使用するか、2 番目の一致を使用するかを設定します。
SELECT REGEXP_REPLACE('TooDB', 'o', 'i',1,1);
+---------------------------------------+
| REGEXP_REPLACE('TooDB', 'o', 'i',1,1) |
+---------------------------------------+
| TioDB |
+---------------------------------------+
1 row in set (0.00 sec)
SELECT REGEXP_REPLACE('TooDB', 'o', 'i',1,2);
+---------------------------------------+
| REGEXP_REPLACE('TooDB', 'o', 'i',1,2) |
+---------------------------------------+
| ToiDB |
+---------------------------------------+
1 row in set (0.00 sec)
次の例では、6 番目の引数を使用して、大文字と小文字を区別しない一致にmatch_type設定します。正規表現match_typeの詳細については、 match_type互換性を参照してください。
SELECT REGEXP_REPLACE('TooDB', 'O{2}','i',1,1);
+-----------------------------------------+
| REGEXP_REPLACE('TooDB', 'O{2}','i',1,1) |
+-----------------------------------------+
| TooDB |
+-----------------------------------------+
1 row in set (0.00 sec)
SELECT REGEXP_REPLACE('TooDB', 'O{2}','i',1,1,'i');
+---------------------------------------------+
| REGEXP_REPLACE('TooDB', 'O{2}','i',1,1,'i') |
+---------------------------------------------+
| TiDB |
+---------------------------------------------+
1 row in set (0.00 sec)
REGEXP_SUBSTR()
正規表現に一致する部分文字列を返します（MySQLと部分的に互換性があります。詳細についてはMySQL との正規表現の互換性を参照してください）。
REGEXP_SUBSTR(str, regexp, [start, [match, [match_type]]])関数は、正規表現に基づいて部分文字列を取得するために使用されます。
次の例では、正規表現Ti.{2}を使用して、文字列This is TiDBのサブ文字列TiDBを取得します。
SELECT REGEXP_SUBSTR('This is TiDB','Ti.{2}');
+----------------------------------------+
| REGEXP_SUBSTR('This is TiDB','Ti.{2}') |
+----------------------------------------+
| TiDB |
+----------------------------------------+
1 row in set (0.00 sec)
REPEAT()
文字列を指定された回数繰り返します。
例:
次の例では、 再帰共通テーブル式 (CTE)を使用して 1 から 20 までの数字のシーケンスを生成します。シーケンス内の各数字に対して、文字xその数字と同じ回数繰り返されます。
WITH RECURSIVE nr(n) AS (
SELECT 1 AS n
UNION ALL
SELECT n+1 FROM nr WHERE n<20
)
SELECT n, REPEAT('x',n) FROM nr;
+------+----------------------+
| n | REPEAT('x',n) |
+------+----------------------+
| 1 | x |
| 2 | xx |
| 3 | xxx |
| 4 | xxxx |
| 5 | xxxxx |
| 6 | xxxxxx |
| 7 | xxxxxxx |
| 8 | xxxxxxxx |
| 9 | xxxxxxxxx |
| 10 | xxxxxxxxxx |
| 11 | xxxxxxxxxxx |
| 12 | xxxxxxxxxxxx |
| 13 | xxxxxxxxxxxxx |
| 14 | xxxxxxxxxxxxxx |
| 15 | xxxxxxxxxxxxxxx |
| 16 | xxxxxxxxxxxxxxxx |
| 17 | xxxxxxxxxxxxxxxxx |
| 18 | xxxxxxxxxxxxxxxxxx |
| 19 | xxxxxxxxxxxxxxxxxxx |
| 20 | xxxxxxxxxxxxxxxxxxxx |
+------+----------------------+
20 rows in set (0.01 sec)
次の例は、 REPEAT()複数の文字で構成される文字列に対して操作できることを示しています。
SELECT REPEAT('ha',3);
+----------------+
| REPEAT('ha',3) |
+----------------+
| hahaha |
+----------------+
1 row in set (0.00 sec)
REPLACE()
指定された文字列の出現箇所を置き換えます。
REVERSE()
文字列内の文字を逆にします。
RIGHT()
指定された右端の文字数を返します。
RLIKE
REGEXPの同義語。
RPAD()
文字列を指定された回数だけ追加します。
RTRIM()
末尾のスペースを削除します。
SPACE()
指定された数のスペースの文字列を返します。
STRCMP()
2 つの文字列を比較します。
SUBSTR()
指定された部分文字列を返します。
SUBSTRING()
指定された部分文字列を返します。
SUBSTRING_INDEX()
SUBSTRING_INDEX()関数は、指定された区切り文字とカウントに基づいて文字列から部分文字列を抽出するために使用されます。この関数は、CSV データの解析やログ ファイルの処理など、特定の区切り文字で区切られたデータを扱う場合に特に便利です。
構文：
SUBSTRING_INDEX(str, delim, count)
str: 処理する文字列を指定します。delim: 文字列内の区切り文字を指定します。大文字と小文字が区別されます。count: 区切り文字の出現回数を指定します。countが正の数の場合、関数は区切り文字のcount回目の出現 (文字列の左から数えて) の前の部分文字列を返します。countが負の数の場合、関数は区切り文字のcount目の出現後 (文字列の右から数えて) の部分文字列を返します。countが0の場合、関数は空の文字列を返します。
例1:
SELECT SUBSTRING_INDEX('www.tidbcloud.com', '.', 2);
出力1:
+-----------------------------------------+
| SUBSTRING_INDEX('www.tidbcloud.com', '.', 2) |
+-----------------------------------------+
| www.tidbcloud |
+-----------------------------------------+
例2:
SELECT SUBSTRING_INDEX('www.tidbcloud.com', '.', -1);
出力2:
+------------------------------------------+
| SUBSTRING_INDEX('www.tidbcloud.com', '.', -1) |
+------------------------------------------+
| com |
+------------------------------------------+
TO_BASE64()
TO_BASE64()関数は、指定された引数を base-64 エンコード形式の文字列に変換し、現在の接続の文字セットと照合順序に従って結果を返すために使用されます。base-64 エンコードされた文字列は、 FROM_BASE64()関数を使用してデコードできます。
構文：
TO_BASE64(str)
- 引数が文字列でない場合、関数はそれを base-64 エンコードする前に文字列に変換します。
- 引数が
NULLの場合、関数はNULL返します。
例1:
SELECT TO_BASE64('abc');
出力1:
+------------------+
| TO_BASE64('abc') |
+------------------+
| YWJj |
+------------------+
例2:
SELECT TO_BASE64(6);
出力2:
+--------------+
| TO_BASE64(6) |
+--------------+
| Ng== |
+--------------+
TRANSLATE()
文字列内のすべての文字を他の文字に置き換えます。Oracle のように空の文字列をNULLとして扱いません。
TRIM()
先頭と末尾のスペースを削除します。
UCASE()
UCASE()関数は文字列を大文字に変換するために使用されます。この関数はUPPER()関数と同等です。
注記：
文字列が null の場合、
UCASE()関数はNULLを返します。
例：
SELECT UCASE('bigdata') AS result_upper, UCASE(null) AS result_null;
出力：
+--------------+-------------+
| result_upper | result_null |
+--------------+-------------+
| BIGDATA | NULL |
+--------------+-------------+
UNHEX()
UNHEX()関数は、 HEX()関数の逆の操作を実行します。引数内の各文字のペアを 16 進数として扱い、その数値で表される文字に変換して、結果をバイナリ文字列として返します。
注記：
引数は、
0～9、A～F、またはa～fを含む有効な 16 進数値である必要があります。引数がNULLまたはこの範囲外の場合、関数はNULLを返します。
例：
SELECT UNHEX('54694442');
出力：
+--------------------------------------+
| UNHEX('54694442') |
+--------------------------------------+
| 0x54694442 |
+--------------------------------------+
UPPER()
UPPER()関数は文字列を大文字に変換するために使用されます。この関数はUCASE()関数と同等です。
注記：
文字列が null の場合、
UPPER()関数はNULLを返します。
例：
SELECT UPPER('bigdata') AS result_upper, UPPER(null) AS result_null;
出力：
+--------------+-------------+
| result_upper | result_null |
+--------------+-------------+
| BIGDATA | NULL |
+--------------+-------------+
WEIGHT_STRING()
WEIGHT_STRING()関数は、入力文字列の重み文字列 (バイナリ文字) を返します。主に、複数文字セットのシナリオでの並べ替えや比較操作に使用されます。引数がNULLの場合は、 NULLを返します。構文は次のとおりです。
WEIGHT_STRING(str [AS {CHAR|BINARY}(N)])
str: 入力文字列式。CHAR、VARCHAR、TEXTなどの非バイナリ文字列の場合、戻り値には文字列の照合順序重みが含まれます。BINARY、VARBINARY、BLOBなどのバイナリ文字列の場合、戻り値は入力と同じになります。AS {CHAR|BINARY}(N): 出力のタイプと長さを指定するために使用されるオプションのパラメータCHARは文字データ型を表し、BINARYバイナリ データ型を表します。6N出力の長さを指定します。これは 1 以上の整数です。
注記：
N文字列の長さより短い場合、文字列は切り捨てられます。3N文字列の長さを超える場合、AS CHAR(N)文字列に指定された長さになるまでスペースを埋め込みます。7AS BINARY(N)文字列に指定された長さになるまで0x00を埋め込みます。
例：
SET NAMES 'utf8mb4';
SELECT HEX(WEIGHT_STRING('ab' AS CHAR(3))) AS char_result, HEX(WEIGHT_STRING('ab' AS BINARY(3))) AS binary_result;
出力：
+-------------+---------------+
| char_result | binary_result |
+-------------+---------------+
| 6162 | 616200 |
+-------------+---------------+
サポートされていない関数
LOAD_FILE()MATCH()SOUNDEX()
MySQL との正規表現の互換性
次のセクションでは、 REGEXP_INSTR() 、 REGEXP_LIKE() 、 REGEXP_REPLACE() 、 REGEXP_SUBSTR()を含む、MySQL との正規表現の互換性について説明します。
構文の互換性
MySQL は International Components for Unicode (ICU) を使用して正規表現を実装し、TiDB は RE2 を使用します。2 つのライブラリ間の構文の違いについては、 ICUドキュメントとRE2 構文を参照してください。
match_type互換性
TiDB と MySQL 間のmatch_typeの値オプションは次のとおりです。
TiDB の値オプションは
"c"、"i"、"m"、"s"であり、MySQL の値オプションは"c"、"i"、"m"、"n"、"u"です。TiDB の
"s"MySQL の"n"に相当します。TiDB で"s"設定されている場合、.文字は行末文字 (\n) にも一致します。たとえば、MySQL の
SELECT REGEXP_LIKE(a, b, "n") FROM t1TiDB のSELECT REGEXP_LIKE(a, b, "s") FROM t1と同じです。TiDB は、MySQL で Unix のみの行末を意味する
"u"サポートしていません。
match_type | マイグレーション | ティビ | 説明 |
|---|---|---|---|
| c | はい | はい | 大文字と小文字を区別する一致 |
| 私 | はい | はい | 大文字と小文字を区別しないマッチング |
| メートル | はい | はい | 複数行モード |
| s | いいえ | はい | 改行に一致します。MySQL のnと同じです。 |
| ん | はい | いいえ | 改行に一致します。TiDB のsと同じです。 |
| あなた | はい | いいえ | UNIX™ の行末 |
データ型の互換性
バイナリ文字列型に対する TiDB と MySQL のサポートの違い:
- MySQL は 8.0.22 以降、正規表現関数でバイナリ文字列をサポートしていません。詳細については、 MySQL ドキュメントを参照してください。ただし、実際には、すべてのパラメータまたは戻り値の型がバイナリ文字列である場合、MySQL で正規関数が機能します。それ以外の場合は、エラーが報告されます。
- 現在、TiDB ではバイナリ文字列の使用が禁止されており、どのような状況でもエラーが報告されます。
その他の互換性
TiDB で空の文字列を置き換える動作は MySQL とは異なります。1
REGEXP_REPLACE("", "^$", "123")例に挙げます。- MySQL は空の文字列を置き換えず、結果として
""を返します。 - TiDB は空の文字列を置き換え、結果として
"123"を返します。
- MySQL は空の文字列を置き換えず、結果として
TiDB でグループをキャプチャするために使用されるキーワードは、MySQL とは異なります。MySQL ではキーワードとして
$使用されますが、TiDB ではキーワードとして\\が使用されます。また、TiDB では0から9までの番号のグループのみをキャプチャできます。たとえば、次の SQL ステートメントは TiDB に
ab返します。SELECT REGEXP_REPLACE('abcd','(.*)(.{2})$','\\1') AS s;