文字列関数
文字列の長さを返すLENGTH関数、指定文字の出現位置を返すPOSITION関数、文字列を置換するREPLACE関数、指定した文字の位置と長さにマッチする文字列を取り出すSUBSTRING関数などはよく使う関数です。
文字の位置を扱う関数では、先頭位置は 1 です。
ASCII(X)
ASCIIコード変換
ASCII()は、引数XのASCIIコード値を返します。引数が空文字の場合は 0、NULLの場合はNULLを返します。ASCIIコードを文字列に変換するには、CHAR()を使用します。
SELECT ASCII( 'A' ), ASCII( 'B' ), ASCII( 'C' ); -> 65 66 67
引数に複数の文字がある場合は、左端の文字のみがACIIコード値に変換されて返されます。
SELECT ASCII( 'ABC' ); -> 65
BIN(X)
2進数
BIN()は、引数Xで指定した数値のバイナリの文字列式を返します。意味的にCONV(N, 10, 2)と同じです。
引数XがNULLであればそのままNULLを返します。
SELECT BIN( 2 ); -> 10 SELECT BIN( 4 ); -> 100
- バイナリ
- バイナリとは、テキスト形式以外のデータ形式全般のことを意味します。実行可能形式のコンピュータプログラムや、画像や音声、動画などのデータなどがバイナリデータにあたります。コンピュータが直接実行できるフォーマットの実行ファイルを特にバイナリコードと呼びます。バイナリの原義は「二進数の」という意味です。
BIT_LENGTH(X)
文字列の長さをビットで返す
引数Xで指定した文字列の長さをビットで返します。
SELECT BIT_LENGTH( 'rhythm' ); -> 48
CHAR(N, ...)
アスキーコードから文字を返す
CHAR()は、引数で指定した値のASCII コード値から対応する文字列を返します。NULLはスキップします。文字をASCIIコードに変換するにはASCII()を使用します。
SELECT CHAR( 82, 104, 121, 116, 104, 109 ); -> Rhythm
CHAR_LENGTH(X)
CHARACTER_LENGTH(X)
LENGTH(X)
OCTET_LENGTH(X)
文字列の文字数を返す
CHARACTER_LENGTH()は、引数Xで指定した文字列の文字数を返します。
CHAR_LENGTH()は、CHARACTER_LENGTHの別名です。
SELECT CHARACTER_LENGTH( 'rhythmfactory' ); -> 13
マルチバイト文字はバイト数ではなく文字数でカウントされます。
SELECT CHARACTER_LENGTH( 'リズムファクトリー' ); -> 9
- マルチバイト文字
- ASCII文字は1文字1バイトですが、漢字のような複雑な文字は1バイトで表現できないため、複数バイトが使われます。このように複数バイトで1文字を表す文字のことをマルチバイト文字と呼びます。
CONCAT(S1, S2, ...)
文字列の結合
CONCAT()は、複数の引数を結合した結果を返します。
標準SQLでは || で文字列を結合しますが、MySQLでは || と && 演算子は論理OR、ANDを意味すると解釈されます。その結果、|| で文字列を結合することはできませんが、代わりとしてCONCAT()関数が用意されています。
SELECT CONCAT( 'Rhythm', 'Factory' ); -> RhythmFactory
引数リストにNULLが含まれる場合、CONCAT()はNULLを返します。
SELECT CONCAT( 'Rhythm', 'Factory', NULL ); -> NULL
CONCAT_WS(SEPARATOR, S1, S2, ...)
文字列の結合
CONCAT_WS()は、CONCAT()の特殊な形式です。結合したい文字列のリストの前に、区切り文字を指定することができます。引数SEPARATORに区切り文字を指定すると、結合する文字列の間に指定した区切り文字が挿入されます。区切り文字がNULLの場合、結果はNULLになります。
SELECT CONCAT_WS( '-','Rhythm','Factory' ); -> Rhythm-Factory
区切り文字より後のNULLをスキップしますが、空文字列はスキップしません。
# NULL文字はスキップされるため、区切り文字は1つです SELECT CONCAT_WS( '-', 'Rhythm', NULL, 'Factory' ); -> Rhythm-Factory # 空文字はスキップされないため、区切り文字は2つです SELECT CONCAT_WS( '-', 'Rhythm' ,'', 'Factory' ); -> Rhythm--Factory
CONV(N, FROM, TO)
進数変換
CONV()は、引数Nで指定した数値の基数をFROM進数からTO進数に変換し、その文字列を返します。引数がNULLの場合は、NULLを返します。最小の基数は2進数で、最大は36進数です。 引数TOが負数であれば、引数Nは符号付きの数値になります。
次の例では、10進数の値"8"を2進数に変換しています。
SELECT CONV( 8, 10, 2 ); -> 1000
ELT(N, STR1, STR2, ...)
文字セットのN番目を返す
ELT()は、引数Nの値が"1"ならSTR1を、値が"2"ならSTR2を返します。引数Nが"1"より小さい場合、または引数の数より大きい場合はNULLを返します。
SELECT ELT( 2, 'Rhythm', 'Factory' ); -> Factory
EXPORT_SET(BITS, ON, OFF, [SEPARATOR, [NUMBER_OF_BITS]])
引数BITSの数値に対し、1ビットを引数ON、ビット0を引数OFFで変換し、区切り文字で区切った文字列を返す
EXPORT_SET()は、引数BITSで指定した数値に対し、1のビットを引数ONで指定した文字列、0のビットを引数OFFで指定した文字列で返します。それぞれの文字は引数SEPARATOR（デフォルトはカンマ( , ）で区切り、引数NUMBER_OF_BITS (デフォルトは 64) で指定された数のビットだけを変換の対象にします。
SELECT EXPORT_SET( 1, 'Y', 'N', ' ',4 ); -> Y N N N
FIELD(STR, STR1, STR2, STR3, ...)
引数リストの位置を返す
FIELD()は、第1引数で指定した文字列にマッチする第2引数以降の引数リストの位置を返します。引数STRが見つからなければ"0"を返します。ELT()の逆です。
SELECT FIELD( 'Monkey', 'www', 'Space', 'Monkey' ); -> 3
FIND_IN_SET(STR, STRLIST)
指定文字列が文字列セットの何番目と一致するか
FIND_IN_SET()は、第1引数で指定した文字列が、引数STRLISTに含まれる値にマッチすれば、マッチしたリストの位置を返します。 STRLISTは、カンマ( , )で分割された文字列です。引数STRLISTが空文字なら"0"を返します。どちらかの引数がNULLならNULLを返します。最初の引数にカンマ( , )が含まれる場合は、エラーとなります。
SELECT FIND_IN_SET( 'Mon', 'Spa,ce,Mon,eky' ); -> 3 SELECT FIND_IN_SET( 'ce', 'Spa,ce,Mon,eky' ); -> 2
HEX(N)
16進数を返す
HEX()は、引数Nの16進数値を表す文字列を返します。引数Nは longlong(BIGINT) 数値です。引数NがNULLの場合はNULLを返します。CONV(N,10,16)と同じです。
SELECT HEX( 255 ); -> 'FF'
INSERT(STR, POS, LEN, NEWSTR)
指定位置の文字列置換
INSERT()は、引数STRで指定した文字列中を、POS位置から長さLENの文字列をNEWSTRで置き換えて返します。引数STR内の最初の位置は1です。
SELECT INSERT( 'RhythmPactory', 7, 4, 'Fact' ); -> RhythmFactory
引数LENに 0 を指定すると、文字列の置き換えなしで指定した文字列が挿入されます。
SELECT INSERT( 'RhythmPactory', 7, 0, 'Fact' ); -> RhythmFactPactory
INSTR(STR, SUBSTR)
指定文字の出現位置
INSTR()は、引数STRで指定した文字列から、引数SUBSTRで指定した文字列とマッチした位置を返します。引数が入れ替わっていることをのぞいて、2つの引数を与えたLOCATE()と同じす。
SELECT INSTR( 'SpaceMonkey', 'ce' ); -> 4
LCASE(X) /
LOWER(X)
小文字化
LCASE() / LOWER()は引数Xに含まれる大文字を小文字に変換して返します。UCASE() / UPPER()は引数Xに含まれる小文字を大文字に変換して返します。
変換は現在の文字列セットマッピング (デフォルト ISO-8859-1 Latin1) に従って行われます。
SELECT LCASE( 'SpaceMonkey' ); -> spacemonkey
LEFT(STR, LEN)
文字列を左から切り取る
LEFT()は、引数STRで指定した文字列の先頭からLEN個の文字を取り出します。
SELECT LEFT( 'SpaceMonkey', 5 ); -> Space
LOAD_FILE(FILE_NAME)
ファイルの読み込み
LOAD_FILE()は、ローカルにあるファイルを読み込み、ファイルの中身を文字列として返します。
ファイル名はフルパスで指定します。その際、ファイル権限が必要です。
ファイルは全員に読み込み可能でなければならず、max_allowed_packetより小さいサイズでなければなりません。
このうちどれかの理由で、もしファイルが存在しないか読み込めない場合、この関数はNULLを返します。
UPDATE テーブル名 SET フィールド名=LOAD_FILE("ディレクトリパス") WHERE フィールド名=値;
LPAD(STR, LEN, PADSTR)
左文字埋め
LPAD()は、引数STRで指定した文字列の長さがLENになるまで、先頭にPADSTRで指定した文字列を埋めます。
SELECT LPAD( 'Space', 10, '+' ); -> +++++Space
LPAD()は、数値の先頭に0で埋めて桁合わせするのにも便利です。
SELECT LPAD( 9, 3, '0' ); -> 009
LTRIM(str) / RTRIM(str) / TRIM
空白文字の切り取り
TRIM([[BOTH | LEADING | TRAILING] [REMSTR] FROM] STR)
LTRMI()は、引数STRで指定した文字列の先頭から、RTRIMはSTRの末尾から空白文字を削除します。
SELECT LTRIM( ' SpaceMonkey' ); -> SpaceMonkey SELECT RTRIM( 'SpaceMonkey ' ); -> SpaceMonkey
TRIMは、先頭の空白と末尾の空白の両方、もしくは一方を削除するように指定することができます。第1引数のデフォルトはBOTHです。REMSTRが与えられないと、空白が削除されます。
SELECT TRIM( LEADING 'x' FROM 'xxxSpaceMonkeyxxx' ); -> SpaceMonkeyxxx SELECT TRIM( BOTH 'x' FROM 'xxxSpaceMonkeyxxx' ); -> SpaceMonkey SELECT TRIM( TRAILING 'x' FROM 'xxxSpaceMonkeyxxx' ); -> xxxSpaceMonkey
LOCATE(SUBSTR, STR, POS)
POSITION(SUBSTR IN STR)
指定文字の出現位置
LOCATE()は、引数STRで指定した文字列内から、引数SUBSTRで指定した文字列がマッチした位置を返します。引数POSはマッチングを開始する位置で、POSを指定しないときのデフォルトの位置は1です。STR内にSUBSTRがない時は0を返します。
SELECT LOCATE( 'e', 'SpaceMonkey' ); -> 5 SELECT LOCATE( 'e', 'SpaceMonkey', 6 ); -> 10
MAKE_SET(BITS, STR1, STR2, ...)
ビットに対応する文字リスト
MAKE_SET()は、引数BITSに指定されたビットに対応する文字列のセットを返します(文字列が複数の場合、 カンマ( ,
)で区切られます) 。STR1がビット0、STR2がビット1というように対応します。引数リストに含まれているNULL文字は結果に含まれません。
SELECT MAKE_SET( 3, 'Space', 'Monkey', '!' ); -> Space,Monkey SELECT MAKE_SET( 1 | 4, 'Space', 'Monkey', '!' ); -> Space,!
OCT(X)
8進数を表す文字列を返す
OCT()は、引数Xで指定した値の8進数を表す文字列表現を返します。Nはlonglong(BIGINT) 数値です。OCTは、意味的にCONV(N, 10, 8) と同じです。引数NがNULLの場合はNULLを返します。
SELECT OCT( 8 ); -> 10
OCTET_LENGTH(X)
文字数をバイト単位で返す
OCTET_LENGTHは引数Xに指定した文字列の文字数をバイト単位で返します。
SELECT OCTET_LENGTH('スペースモンキー');
-> 24
ORD(X)
マルチバイト文字のコードを返す
ORD()は、引数Xで指定した文字列の先頭がマルチバイト文字の場合、次のフォーマットで文字のASCIIコードを返すことにより、マルチバイト文字のコードを返します。
((first byte ASCII code) * 256 + (second byte ASCII
code)) [ * 256 + third byte ASCII code...]
先頭の文字がマルチバイト文字でない場合は、ASCII()と同じ値を返します。
SELECT ORD( '純' ); -> 15185044
REPEAT(SR, COUNT)
文字列の指定回数繰り返し
REPEAT()は、引数STRをCOUNT回繰り返した文字列を返します。COUNTが 0
以下の場合は空文字を返します。
SELECT REPEAT( 'Yes!', 3 ); -> Yes!Yes!Yes!
引数COUNTに負の数値を指定すると空文字が返されます。
SELECT REPEAT( 'Yes!', -1 ); ->
REPLACE(STR, FROM_STR, TO_STR)
文字列の置換
REPLACE()は、引数STRに指定した文字列から、引数FROM_STRにマッチした文字をすべて引数TO_STRに置き換えます。
SELECT REPLACE( 'www spacemonkey jp', ' ', '.' ); -> www.spacemonkey.jp
REVERSE(X)
文字列の逆順
REVERSE()は、引数Xで指定した文字列の順序を逆にして返します。
SELECT REVERSE( 'SpaceMonkey' ); -> yeknoMecapS
RPAD(STR, LEN, PADSTR)
右文字埋め
RPAD()は、引数STRの長さがLENになるまで末尾にPADSTRを埋めます。引数LENで指定する文字列長はバイトで指定します。
SELECT RPAD( 'SpaceMonkey', 15, '-' ); -> SpaceMonkey----
RIGHT(STR, LEN)
SUBSTRING(STR from LEN)
文字列を右から切り取る
RIGHT()は、引数STRで指定した文字列の末尾から、LEN個の文字を返します。引数LENには、MySQLではバイト数、そのほかのDBMSは文字数を指定します。
SELECT RIGHT( 'SpaceMonkey', 6 ); -> Monkey SELECT SUBSTRING( 'SpaceMonkey' FROM 6 ); -> Monkey
SUBSTRING()は3つの引数を受け取ることもできます。その場合は、1番目が対象となる文字列で、2番目が部分文字列の開始、3番目が、部分文字列の終了位置になります。
次の例は、文字列の先頭から7番目の文字、それから3文字分を返します。
SELECT SUBSTRING( 'SpaceMonkey', 6, 3 ); -> Mon
SOUNDEX(X)
あいまい検索
SOUNDEX()は、引数Xで指定した文字列に関連するSOUNDEXコードを返します。
引数Xに含まれる非アルファベット文字は無視されます。AからZの範囲外の国際的なアルファベット文字は母音とみなされます。
SELECT SOUNDEX( 'Space' ); -> S120 SELECT SOUNDEX( 'Monkey' ); -> M200
SPACE(X)
空白文字を返します
SPACE()は、引数Xで指定した数値の文だけ空白文字を返します。
SELECT SPACE( 2 ); -> ' '
SUBSTRING(STR, POS, LEN)
MID(STR, POS, LEN)
SUBSTRING(STR from POS for LEN)
文字列切り出し
SUBSTRING()は、引数STRのPOS位置からLEN文字数分の文字列を返します。
SELECT SUBSTRING( 'SpaceMonkey', 6, 3 ); -> Mon
引数LENが指定されない場合はそれ以降の文字列全てを返します。
SELECT SUBSTRING( 'SpaceMonkey', 6 ); -> Monkey
SUBSTRING_INDEX(STR, DELIM, COUNT)
文字列の切り出し
SUBSTRING_INDEX()は、引数STRで指定した文字列中に、区切り文字DELIMがCOUNT番目に表れる位置から前にある文字列を返します。
SELECT SUBSTRING_INDEX( 'www.rfs.jp', '.', 1 ); -> www
引数COUNTが正の場合は、文字列は左から、引数COUNTが負の場合は右からマッチングを行います。
SELECT SUBSTRING_INDEX( 'www.rfs.jp', '.', -2 ); -> rfs.jp