第4章 関数

文字列関数

2009年8月17日

目次

文字列関数

文字列の長さを返す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)
小文字化
UCASE(X) / UPPER(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

関連記事

Comment

コメントを残す

メールアドレスが公開されることはありません。

リズムファクトリーはホームページの制作会社です。
ホームページ制作に関するご要望・ご相談はこちらからどうぞ。