MySQLのお勉強!

MySQL TOP

12.5 文字列関数

表 12.7 文字列演算子

名前 説明
ASCII() 左端の文字の数値を返します
BIN() 数値のバイナリ表現を含む文字列を返します
BIT_LENGTH() ビット単位で引数の長さを返します
CHAR() 渡された各整数の文字を返します
CHAR_LENGTH() 引数の文字数を返します
CHARACTER_LENGTH() CHAR_LENGTH() のシノニムです
CONCAT() 連結された文字列を返します
CONCAT_WS() 連結されたものをセパレータ付きで返します
ELT() インデックス番号位置の文字列を返します
EXPORT_SET() 値 bits 内の各ビットが設定されている場合は on 文字列を取得し、各ビットが設定されていない場合には off 文字列を取得するように、文字列を返します
FIELD() 後続の引数内で第 1 引数のインデックス (位置) を返します
FIND_IN_SET() 第 2 引数内で第 1 引数のインデックス位置を返します
FORMAT() 指定された小数点以下桁数に書式設定された数値を返します
FROM_BASE64() (導入 5.6.1) base 64 文字列にデコードして結果を返します
HEX() 10 進値または文字列値の 16 進表現を返します
INSERT() 部分文字列を、指定された位置に指定された文字数だけ挿入します
INSTR() 部分文字列が最初に出現する位置のインデックスを返します
LCASE() LOWER() のシノニムです
LEFT() 左端から指定された数の文字を返します
LENGTH() 文字列の長さをバイト単位で返します
LIKE 単純なパターン一致
LOAD_FILE() 指定されたファイルをロードします
LOCATE() 部分文字列が最初に出現する位置を返します
LOWER() 引数を小文字で返します
LPAD() 指定された文字列で左からパディングした文字列引数を返します
LTRIM() 先頭の空白を削除します
MAKE_SET() bits セット内の対応するビットを持つ、カンマ区切り文字列のセットを返します
MATCH 全文検索を実行します
MID() 指定された位置から始まる部分文字列を返します
NOT LIKE 単純なパターン一致の否定
NOT REGEXP REGEXP の否定
OCT() 数値の 8 進数表現を含む文字列を返します
OCTET_LENGTH() LENGTH() のシノニムです
ORD() 引数の左端の文字の文字コードを返します
POSITION() LOCATE() のシノニムです
QUOTE() SQL ステートメント内で使用するために引数をエスケープします
REGEXP 正規表現を使用したパターン一致
REPEAT() 文字列を指定された回数だけ繰り返します
REPLACE() 指定された文字列の出現箇所を置き換えます
REVERSE() 文字列内の文字を逆順に並べ替えます
RIGHT() 右端から指定された数の文字を返します
RLIKE REGEXP のシノニムです
RPAD() 指定された回数だけ文字列を追加します
RTRIM() 末尾の空白を削除します
SOUNDEX() soundex 文字列を返します
SOUNDS LIKE 音声を比較します
SPACE() 指定された数の空白で構成される文字列を返します
STRCMP() 2 つの文字列を比較します
SUBSTR() 指定された部分文字列を返します
SUBSTRING() 指定された部分文字列を返します
SUBSTRING_INDEX() 文字列から、区切り文字が指定された回数出現する前の部分文字列を返します
TO_BASE64() (導入 5.6.1) base 64 文字列に変換された引数を返します
TRIM() 先頭と末尾にある空白を削除します
UCASE() UPPER() のシノニムです
UNHEX() 数値の 16 進数表現を含む文字列を返します
UPPER() 大文字に変換します
WEIGHT_STRING() 文字列の重み文字列を返します

文字列値の関数は、結果の長さが max_allowed_packet システム環境変数の値よりも長くなると、NULL を返します。セクション8.11.2「サーバーパラメータのチューニング」を参照してください。

文字列の位置を操作する関数では、最初の位置には数値 1 が付けられます。

長さの引数を取る関数では、整数以外の引数はもっとも近い整数に丸められます。

  • ASCII(str)

    文字列 str の左端の文字の数値を返します。str が空の文字列である場合は、0 を返します。strNULL である場合は NULL を返します。ASCII() は、8 ビット文字の場合に動作します。

    mysql> SELECT ASCII('2');
            -> 50
    mysql> SELECT ASCII(2);
            -> 50
    mysql> SELECT ASCII('dx');
            -> 100

    ORD() 関数も参照してください。

  • BIN(N)

    N のバイナリ値の文字列表現を返します。N は longlong (BIGINT) 数字です。これは、CONV(N,10,2) と同等です。NNULL である場合は NULL を返します。

    mysql> SELECT BIN(12);
            -> '1100'
  • BIT_LENGTH(str)

    文字列 str の長さをビット単位で返します。

    mysql> SELECT BIT_LENGTH('text');
            -> 32
  • CHAR(N,... [USING charset_name])

    CHAR() は各 N 引数を整数として解釈し、それらの整数のコード値で指定された文字を構成している文字列を返します。NULL 値はスキップされます。

    mysql> SELECT CHAR(77,121,83,81,'76');
            -> 'MySQL'
    mysql> SELECT CHAR(77,77.3,'77.3');
            -> 'MMM'

    255 よりも大きい CHAR() 引数は、複数の結果バイトに変換されます。たとえば、CHAR(256)CHAR(1,0) に同等で、CHAR(256*256)CHAR(1,0,0) に同等です。

    mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
    +----------------+----------------+
    | HEX(CHAR(1,0)) | HEX(CHAR(256)) |
    +----------------+----------------+
    | 0100           | 0100           |
    +----------------+----------------+
    mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));
    +------------------+--------------------+
    | HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |
    +------------------+--------------------+
    | 010000           | 010000             |
    +------------------+--------------------+

    デフォルトでは、CHAR() はバイナリ文字列を返します。指定された文字セットで文字列を生成するには、オプションの USING 句を使用します。

    mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
    +---------------------+--------------------------------+
    | CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |
    +---------------------+--------------------------------+
    | binary              | utf8                           |
    +---------------------+--------------------------------+

    USING が指定され、結果文字列が指定された文字セットで不正である場合は、警告が発行されます。また、厳密な SQL モードが有効になっている場合は、CHAR() からの結果は NULL になります。

  • CHAR_LENGTH(str)

    文字で測定された文字列 str の長さを返します。マルチバイト文字は、単一の文字としてカウントされます。つまり、5 つの 2 バイト文字を含む文字列では、LENGTH()10 を返し、CHAR_LENGTH()5 を返します。

  • CHARACTER_LENGTH(str)

    CHARACTER_LENGTH()CHAR_LENGTH() のシノニムです。

  • CONCAT(str1,str2,...)

    引数を連結することで生成される文字列を返します。1 つ以上の引数を持つ場合があります。すべての引数が非バイナリ文字列の場合は、結果も非バイナリ文字列になります。引数にバイナリ文字列が含まれる場合は、結果はバイナリ文字列になります。数値の引数は、同等の非バイナリ文字列形式に変換されます。

    引数のいずれかかが NULL である場合、CONCAT()NULL を返します。

    mysql> SELECT CONCAT('My', 'S', 'QL');
            -> 'MySQL'
    mysql> SELECT CONCAT('My', NULL, 'QL');
            -> NULL
    mysql> SELECT CONCAT(14.3);
            -> '14.3'

    引用符で囲まれた文字列では、文字列を並べて配置することで連結が実行されます。

    mysql> SELECT 'My' 'S' 'QL';
            -> 'MySQL'
  • CONCAT_WS(separator,str1,str2,...)

    CONCAT_WS() は Concatenate With Separator (区切り文字を使用した連結) を表し、CONCAT() の特殊な形式です。最初の引数は、残りの引数の区切り文字です。区切り文字は、連結される文字列の間に追加されます。区切り文字は、残りの引数と同様に文字列にすることができます。区切り文字が NULL の場合は、結果も NULL になります。

    mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
            -> 'First name,Second name,Last Name'
    mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
            -> 'First name,Last Name'

    CONCAT_WS() では、空の文字列がスキップされません。ただし、区切り文字引数のあとの NULL 値はすべてスキップされます。

  • ELT(N,str1,str2,str3,...)

    ELT() は、文字列リストの N 番目の要素を返します。N = 1 の場合は str1N = 2 の場合は str2 のように返します。N1 よりも小さいか、引数の数よりも大きい場合は、NULL を返します。ELT()FIELD() の補数です。

    mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
            -> 'ej'
    mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
            -> 'foo'
  • EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

    bits 内で各ビットが設定されている場合には on 文字列を取得し、値内で各ビットが設定されていない場合には off 文字列を取得するように、文字列を返します。bits のビットは、右から左 (下位ビットから上位ビット) へと検証されます。文字列は、separator 文字列 (デフォルトはカンマ文字 ,) で区切られた結果に左から右へと追加されます。検証されるビット数は、number_of_bits で指定されます。指定されない場合のデフォルトは 64 です。number_of_bits が 64 よりも大きい場合は、警告なしで 64 に短縮されます。符号なし整数として処理されるため、値 −1 は実際には 64 と同じです。

    mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
            -> 'Y,N,Y,N'
    mysql> SELECT EXPORT_SET(6,'1','0',',',10);
            -> '0,1,1,0,0,0,0,0,0,0'
  • FIELD(str,str1,str2,str3,...)

    str1str2str3... リスト内で str のインデックス (位置) を返します。str が見つからない場合は、0 を返します。

    FIELD() へのすべての引数が文字列の場合は、すべての引数が文字列として比較されます。すべての引数が数値の場合は、数字として比較されます。それ以外の場合は、引数が倍精度として比較されます。

    NULL ではどの値との等価比較にも失敗するため、strNULL である場合は、戻り値が 0 になります。FIELD()ELT() の補数です。

    mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
            -> 2
    mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
            -> 0
  • FIND_IN_SET(str,strlist)

    文字列 strN 部分文字列で構成される文字列リスト strlist 内にある場合は、1 から N までの範囲内の値を返します。文字列リストは、, 文字で区切られた部分文字列で構成された文字列です。最初の引数が定数文字列で、2 番目が SET 型のカラムの場合、FIND_IN_SET() 関数はビット演算を使用するために最適化されます。strstrlist 内にない場合、または strlist が空の文字列の場合は、0 を返します。引数のいずれかが NULL である場合は、NULL を返します。最初の引数にカンマ (,) 文字が含まれる場合は、この関数が正しく動作しません。

    mysql> SELECT FIND_IN_SET('b','a,b,c,d');
            -> 2
  • FORMAT(X,D[,locale])

    数値 X'#,###,###.##' のような書式に変換し、小数点第 D 位に丸めて、その結果を文字列として返します。D0 の場合は、結果に小数点または小数部が含まれません。

    オプションの 3 番目のパラメータを使用すると、結果数の小数点、3 桁の区切り文字、および区切り文字間のグループ化に使用されるロケールを指定できます。許可されるロケール値は、lc_time_names システム変数の有効な値と同じです (セクション10.7「MySQL Server のロケールサポート」を参照してください)。ロケールが指定されていない場合のデフォルトは、'en_US' です。

    mysql> SELECT FORMAT(12332.123456, 4);
            -> '12,332.1235'
    mysql> SELECT FORMAT(12332.1,4);
            -> '12,332.1000'
    mysql> SELECT FORMAT(12332.2,0);
            -> '12,332'
    mysql> SELECT FORMAT(12332.2,2,'de_DE');
            -> '12.332,20'
  • FROM_BASE64(str)

    TO_BASE64() で使用される base-64 でエンコードされたルールでエンコードされた文字列が指定され、デコードされた結果をバイナリ文字列として返します。引数が NULL の場合または有効な base-64 文字列でない場合は、結果が NULL になります。ルールのエンコードおよびデコードについての詳細は、TO_BASE64() の説明を参照してください。

    この関数は、MySQL 5.6.1 で追加されました。

    mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
            -> 'JWJj', 'abc'
  • HEX(str), HEX(N)

    文字列の引数 str では、HEX()str の 16 進数文字列表現を返します。str 内の各文字の各バイトは、2 つの 16 進数字に変換されます。(したがって、マルチバイト文字は 2 桁よりも大きくなります。)この演算の逆は、UNHEX() 関数で実行されます。

    数値の引数 N では、HEX() は、longlong (BIGINT) 数字として処理される N の 16 進数文字列表現を返します。これは、CONV(N,10.16) と同等です。この演算の逆は、CONV(HEX(N),16,10) で実行されます。

    mysql> SELECT 0x616263, HEX('abc'), UNHEX(HEX('abc'));
            -> 'abc', 616263, 'abc'
    mysql> SELECT HEX(255), CONV(HEX(255),16,10);
            -> 'FF', 255
  • INSERT(str,pos,len,newstr)

    位置 pos で始まる部分文字列と、文字列 newstr で置換された len 文字長とともに、文字列 str を返します。pos が文字列の長さに収まらない場合は、元の文字列を返します。len が残りの文字列の長さに収まらない場合は、位置 pos からの残りの文字列を置換します。引数のいずれかが NULL である場合は、NULL を返します。

    mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
            -> 'QuWhattic'
    mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
            -> 'Quadratic'
    mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
            -> 'QuWhat'

    この関数はマルチバイトセーフです。

  • INSTR(str,substr)

    文字列 str 内で部分文字列 substr が最初に出現する位置を返します。これは、引数の順序が逆になる点を除いて、2 つの引数形式の LOCATE() と同じです。

    mysql> SELECT INSTR('foobarbar', 'bar');
            -> 4
    mysql> SELECT INSTR('xbar', 'foobar');
            -> 0

    この関数はマルチバイトセーフであり、1 つ以上の引数がバイナリ文字列である場合にのみ大文字と小文字が区別されます。

  • LCASE(str)

    LCASE()LOWER() のシノニムです。

  • LEFT(str,len)

    文字列 str から左端の len 文字を返し、引数が NULL である場合は NULL を返します。

    mysql> SELECT LEFT('foobarbar', 5);
            -> 'fooba'

    この関数はマルチバイトセーフです。

  • LENGTH(str)

    バイトで測定された文字列 str の長さを返します。マルチバイト文字は、複数のバイトとしてカウントされます。つまり、5 つの 2 バイト文字を含む文字列では、LENGTH()10 を返し、CHAR_LENGTH()5 を返します。

    mysql> SELECT LENGTH('text');
            -> 4
    注記

    Length() OpenGIS 空間関数は、MySQL では GLength() という名前です。

  • LOAD_FILE(file_name)

    ファイルを読み取り、ファイルの内容を文字列として返します。この関数を使用するには、ファイルがサーバーホストに配置されている必要があり、ファイルへのフルパス名を指定し、FILE 権限を持つ必要があります。ファイルはすべてのユーザーから読み取り可能で、max_allowed_packet バイトよりも小さなサイズである必要があります。secure_file_priv システム変数が空でないディレクトリ名に設定されている場合は、そのディレクトリ内にロード対象のファイルが配置されている必要があります。

    ファイルが存在しない場合、または上記の条件が満たされていないために、ファイルを読み取ることができない場合、この関数は NULL を返します。

    character_set_filesystem システム変数では、リテラル文字列として指定されているファイル名の解釈が制御されます。

    mysql> UPDATE t
                SET blob_col=LOAD_FILE('/tmp/picture')
                WHERE id=1;
  • LOCATE(substr,str), LOCATE(substr,str,pos)

    1 番目の構文は、文字列 str 内で、部分文字列 substr が最初に出現する位置を返します。2 番目の構文は、文字列 str 内の位置 pos 以降で、部分文字列 substr が最初に出現する位置を返します。str 内に substr がない場合は、0 を返します。

    mysql> SELECT LOCATE('bar', 'foobarbar');
            -> 4
    mysql> SELECT LOCATE('xbar', 'foobar');
            -> 0
    mysql> SELECT LOCATE('bar', 'foobarbar', 5);
            -> 7

    この関数はマルチバイトセーフであり、1 つ以上の引数がバイナリ文字列である場合にのみ大文字と小文字が区別されます。

  • LOWER(str)

    現在の文字セットのマッピングに従って、すべての文字が小文字に変更された文字列 str を返します。デフォルトは latin1 (cp1252 西ヨーロッパ言語) です。

    mysql> SELECT LOWER('QUADRATICALLY');
            -> 'quadratically'

    LOWER() (および UPPER()) をバイナリ文字列 (BINARYVARBINARYBLOB) に適用しても、何の効果もありません。大文字/小文字の変換を実行するには、バイナリ文字列を非バイナリ文字列に変換します。

    mysql> SET @str = BINARY 'New York';
    mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
    +-------------+-----------------------------------+
    | LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
    +-------------+-----------------------------------+
    | New York    | new york                          |
    +-------------+-----------------------------------+

    Unicode 文字セットの場合、xxx_unicode_520_ci 照合順序およびそれらから派生した言語固有の照合順序では、LOWER() および UPPER() は Unicode 照合順序アルゴリズム (UCA) 5.2.0 に従って動作します。その他の Unicode 照合では、LOWER() および UPPER() は Unicode 照合アルゴリズム (UCA) 4.0.0 に従って動作します。セクション10.1.14.1「Unicode 文字セット」を参照してください。

    この関数はマルチバイトセーフです。

  • LPAD(str,len,padstr)

    len 文字の長さになるように文字列 padstr で左にパディングされた文字列 str を返します。strlen よりも長い場合は、戻り値は len 文字に短縮されます。

    mysql> SELECT LPAD('hi',4,'??');
            -> '??hi'
    mysql> SELECT LPAD('hi',1,'??');
            -> 'h'
  • LTRIM(str)

    先頭の空白文字が削除された文字列 str を返します。

    mysql> SELECT LTRIM('  barbar');
            -> 'barbar'

    この関数はマルチバイトセーフです。

  • MAKE_SET(bits,str1,str2,...)

    bits セット内の対応するビットを持つ文字列で構成されるセット値 (, 文字で区切られた部分文字列を含む文字列) を返します。str1 はビット 0 に対応し、str2 はビット 1 に対応する、などとなります。str1str2... 内の NULL 値は結果に追加されません。

    mysql> SELECT MAKE_SET(1,'a','b','c');
            -> 'a'
    mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
            -> 'hello,world'
    mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
            -> 'hello'
    mysql> SELECT MAKE_SET(0,'a','b','c');
            -> ''
  • MID(str,pos,len)

    MID(str,pos,len) は、SUBSTRING(str,pos,len) のシノニムです。

  • OCT(N)

    N の 8 進数の文字列表現を返します。N は longlong (BIGINT) 数字です。これは、CONV(N,10.8) と同等です。NNULL である場合は NULL を返します。

    mysql> SELECT OCT(12);
            -> '14'
  • OCTET_LENGTH(str)

    OCTET_LENGTH()LENGTH() のシノニムです。

  • ORD(str)

    文字列 str の左端の文字がマルチバイト文字である場合は、その文字のコードを返します。コードは、次の計算式を使用して、その構成要素の数値から計算されます。

      (1st byte code)
    + (2nd byte code * 256)
    + (3rd byte code * 2562) ...

    左端の文字がマルチバイト文字でない場合は、ORD()ASCII() 関数と同じ値を返します。

    mysql> SELECT ORD('2');
            -> 50
  • POSITION(substr IN str)

    POSITION(substr IN str)LOCATE(substr,str) のシノニムです。

  • QUOTE(str)

    SQL ステートメントで、適切にエスケープされたデータ値として使用できる結果を生成する文字列を引用符で囲みます。一重引用符で囲まれ、バックスラッシュの後ろにバックスラッシュ (\)、一重引用符 (')、ASCII NUL、および Control+Z の各インスタンスが続く文字列が返されます。引数が NULL の場合の戻り値は、一重引用符で囲まれていない単語 NULL です。

    mysql> SELECT QUOTE('Don\'t!');
            -> 'Don\'t!'
    mysql> SELECT QUOTE(NULL);
            -> NULL

    比較するために、セクション9.1.1「文字列リテラル」およびセクション23.7.7.54「mysql_real_escape_string()」で、リテラル文字列に対する引用ルールと C API 内の引用ルールを参照してください。

  • REPEAT(str,count)

    count 回繰り返された文字列 str で構成される文字列を返します。count が 1 よりも小さい場合は、空の文字列を返します。str または countNULL である場合は NULL を返します。

    mysql> SELECT REPEAT('MySQL', 3);
            -> 'MySQLMySQLMySQL'
  • REPLACE(str,from_str,to_str)

    文字列 from_str のすべての出現箇所が文字列 to_str で置換された、文字列 str を返します。REPLACE() は、from_str を検索する際に、大文字と小文字を区別した一致を実行します。

    mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
            -> 'WwWwWw.mysql.com'

    この関数はマルチバイトセーフです。

  • REVERSE(str)

    文字の順序が逆になった文字列 str を返します。

    mysql> SELECT REVERSE('abc');
            -> 'cba'

    この関数はマルチバイトセーフです。

  • RIGHT(str,len)

    文字列 str から右端の len 文字を返し、引数が NULL である場合は NULL を返します。

    mysql> SELECT RIGHT('foobarbar', 4);
            -> 'rbar'

    この関数はマルチバイトセーフです。

  • RPAD(str,len,padstr)

    len 文字の長さになるように文字列 padstr で右にパディングされた文字列 str を返します。strlen よりも長い場合は、戻り値は len 文字に短縮されます。

    mysql> SELECT RPAD('hi',5,'?');
            -> 'hi???'
    mysql> SELECT RPAD('hi',1,'?');
            -> 'h'

    この関数はマルチバイトセーフです。

  • RTRIM(str)

    末尾の空白文字が削除された文字列 str を返します。

    mysql> SELECT RTRIM('barbar   ');
            -> 'barbar'

    この関数はマルチバイトセーフです。