MySQL :: MySQL 5.6 リファレンスマニュアル

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / 関数と演算子 / 文字列関数

12.5 文字列関数

12.5.1 文字列比較関数
12.5.2 正規表現

表 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()`	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()`	base 64 文字列に変換された引数を返します
`TRIM()`	先頭と末尾にある空白を削除します
`UCASE()`	UPPER() のシノニムです
`UNHEX()`	数値の 16 進数表現を含む文字列を返します
`UPPER()`	大文字に変換します
`WEIGHT_STRING()`	文字列の重み文字列を返します

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

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

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

ASCII(str)

文字列 str の左端の文字の数値を返します。str が空の文字列である場合は、0 を返します。str が NULL である場合は 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) と同等です。N が NULL である場合は 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 の場合は str1、N = 2 の場合は str2 のように返します。N が 1 よりも小さいか、引数の数よりも大きい場合は、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,...)

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

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

NULL ではどの値との等価比較にも失敗するため、str が NULL である場合は、戻り値が 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)

文字列 str が N 部分文字列で構成される文字列リスト strlist 内にある場合は、1 から N までの範囲内の値を返します。文字列リストは、「,」文字で区切られた部分文字列で構成された文字列です。最初の引数が定数文字列で、2 番目が SET 型のカラムの場合、FIND_IN_SET() 関数はビット演算を使用するために最適化されます。str が strlist 内にない場合、または strlist が空の文字列の場合は、0 を返します。引数のいずれかが NULL である場合は、NULL を返します。最初の引数にカンマ (「,」) 文字が含まれる場合は、この関数が正しく動作しません。
```
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2
```
FORMAT(X,D[,locale])

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

オプションの 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()) をバイナリ文字列 (BINARY、VARBINARY、BLOB) に適用しても、何の効果もありません。大文字/小文字の変換を実行するには、バイナリ文字列を非バイナリ文字列に変換します。
```
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 を返します。str が len よりも長い場合は、戻り値は 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 に対応する、などとなります。str1、str2、... 内の 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) と同等です。N が NULL である場合は 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.8.7.54「mysql_real_escape_string()」で、リテラル文字列に対する引用ルールと C API 内の引用ルールを参照してください。
REPEAT(str,count)

count 回繰り返された文字列 str で構成される文字列を返します。count が 1 よりも小さい場合は、空の文字列を返します。str または count が NULL である場合は 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 を返します。str が len よりも長い場合は、戻り値は len 文字に短縮されます。
```
mysql> SELECT RPAD('hi',5,'?');
        -> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
        -> 'h'
```
この関数はマルチバイトセーフです。
RTRIM(str)

末尾の空白文字が削除された文字列 str を返します。
```
mysql> SELECT RTRIM('barbar   ');
        -> 'barbar'
```
この関数はマルチバイトセーフです。
SOUNDEX(str)

str から soundex 文字列を返します。ほぼ同じ発音の 2 つの文字列は、同じ soundex 文字列を持つはずです。標準の soundex 文字列の長さは 4 文字ですが、SOUNDEX() 関数は任意の長さの文字列を返します。結果で SUBSTRING() を使用すると、標準の soundex 文字列を取得できます。str 内のアルファベット以外の文字はすべて無視されます。A から Z までの範囲外の国際アルファベット文字はすべて、母音として処理されます。

重要

SOUNDEX() の使用時には、次の制限に注意してください。
- 現在実装されているこの関数は、文字列の言語が英語である場合にのみ機能するように設計されています。その他の言語の文字列では、信頼できる結果が生成されない可能性があります。
- この関数では、文字列でマルチバイト文字セット (utf-8 など) が使用されている場合に、整合性のある結果が生成されることは保証されません。
  
  今後のリリースで、このような制限が解除されることを期待しています。詳細は、Bug #22638 を参照してください。
```
mysql> SELECT SOUNDEX('Hello');
        -> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
        -> 'Q36324'
```
注記

この関数には、オリジナルの Soundex アルゴリズムが実装されています。より人気のある拡張バージョンではありません (この作成者も D. Knuth です)。相違点としては、元のバージョンではまず母音が破棄されてから複製が破棄されますが、拡張バージョンではまず複製が破棄されてから母音が破棄されます。
expr1 SOUNDS LIKE expr2

これは、SOUNDEX(expr1) = SOUNDEX(expr2) と同じです。
SPACE(N)

N 空白文字で構成される文字列を返します。
```
mysql> SELECT SPACE(6);
        -> '      '
```
SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)

SUBSTR() は SUBSTRING() のシノニムです。
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

len 引数を付けない形式では、位置 pos で始まる文字列 str からの部分文字列が返されます。len 引数を付けた形式では、位置 pos で始まる文字列 str からの部分文字列 len 文字長が返されます。FROM を使用する形式は、標準の SQL 構文です。また、pos に負の値を使用することもできます。その場合、部分文字列の先頭は文字列の先頭でなく、文字列の末尾からの pos 文字になります。この関数のどの形式でも、pos で負の値を使用できます。

すべての形式の SUBSTRING() で、部分文字列の抽出が開始される文字列内の最初の文字の位置が 1 とみなされます。
```
mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'
```
この関数はマルチバイトセーフです。

len が 1 よりも小さい場合は、結果が空の文字列になります。
SUBSTRING_INDEX(str,delim,count)

文字列 str から、区切り文字 delim が count 回出現する前の部分文字列を返します。count が正の値の場合は、(左から数えて) 最後の区切り文字の左側にあるすべてが返されます。count が負の値の場合は、(右から数えて) 最後の区切り文字の右側にあるすべてが返されます。SUBSTRING_INDEX() は、delim を検索する際に、大文字と小文字を区別した一致を実行します。
```
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'
```
この関数はマルチバイトセーフです。
TO_BASE64(str)

文字列の引数を base-64 でエンコードされた形式に変換し、その結果を接続文字セットと照合順序が含まれる文字列として返します。引数が文字列でない場合は、変換が実行される前に文字列に変換されます。引数が NULL である場合は、結果も NULL になります。FROM_BASE64() 関数を使用すると、base-64 でエンコードされた文字列をデコードできます。

この関数は、MySQL 5.6.1 で追加されました。
```
mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
        -> 'JWJj', 'abc'
```
さまざまな base-64 エンコードスキームが存在します。これらは、TO_BASE64() および FROM_BASE64() で使用されるエンコードおよびデコードのルールです。
- アルファベット値 62 のエンコードは '+' です。
- アルファベット値 63 のエンコードは '/' です。
- エンコードされた出力は、出力可能な 4 文字のグループで構成されます。入力データの各 3 バイトは、4 文字を使用してエンコードされます。最後のグループが不完全な場合は、長さが 4 になるまで '=' 文字でパディングされます。
- 長い出力を複数の行に分割するために、エンコードされた出力の各 76 文字の後ろに改行が追加されます。
- デコードでは改行、復帰改行、タブ、および空白が認識および無視されます。
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

すべての remstr プリフィクスまたはサフィクスが削除された文字列 str を返します。BOTH、LEADING、TRAILING のいずれの指定子も指定されない場合は、BOTH が指定されたとみなされます。remstr はオプションであり、指定されない場合は空白文字が削除されます。
```
mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'
```
この関数はマルチバイトセーフです。
UCASE(str)

UCASE() は UPPER() のシノニムです。
UNHEX(str)

文字列の引数 str では、UNHEX(str) は引数の各文字ペアを 16 進数として解釈し、その数字で表されたバイトに変換します。戻り値はバイナリ文字列です。
```
mysql> SELECT UNHEX('4D7953514C');
        -> 'MySQL'
mysql> SELECT 0x4D7953514C;
        -> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
        -> 'string'
mysql> SELECT HEX(UNHEX('1267'));
        -> '1267'
```
引数文字列内の文字は、正当な 16 進数である必要があります: '0' .. '9'、'A' ..'F'、'a' .. 'f'。引数に 16 進以外の数字が含まれている場合は、結果が NULL になります。
```
mysql> SELECT UNHEX('GG');
+-------------+
| UNHEX('GG') |
+-------------+
| NULL        |
+-------------+
```
UNHEX() への引数が BINARY カラムである場合は、格納時に値に 0x00 バイトがパディングされますが、それらのバイトは取得時に削除されないため、結果が NULL になる可能性があります。たとえば、'41' は '41 ' として CHAR(3) カラムに格納され、(末尾にパディングされた空白文字が削除された) '41' として取得されるため、カラム値に対する UNHEX() では 'A' が返されます。反対に、'41' は '41\0' として BINARY(3) カラムに格納され、(末尾にパディングされた 0x00 バイトが削除されない) '41\0' として取得されます。'\0' は不正な 16 進数であるため、カラム値に対する UNHEX() では NULL が返されます。

数値の引数 N の場合、HEX(N) の逆は UNHEX() では実行されません。代わりに、CONV(HEX(N),16,10) を使用してください。HEX() の説明を参照してください。
UPPER(str)

現在の文字セットのマッピングに従って、すべての文字が大文字に変更された文字列 str を返します。デフォルトは latin1 (cp1252 西ヨーロッパ言語) です。
```
mysql> SELECT UPPER('Hej');
        -> 'HEJ'
```
UPPER() にも適用される情報については、LOWER() の説明を参照してください。これには、現在は機能が無効になっているバイナリ文字列 (BINARY、VARBINARY、BLOB) の大文字と小文字の変換を実行する方法に関する情報、および Unicode 文字セットの大文字と小文字の変換に関する情報が含まれていました。

この関数はマルチバイトセーフです。
WEIGHT_STRING(str [AS {CHAR|BINARY}(N)] [LEVEL levels] [flags])

levels: N [ASC|DESC|REVERSE] [, N [ASC|DESC|REVERSE]] ...

この関数は、入力文字列の重み文字列を返します。戻り値は、文字列のソートおよび比較の値を表すバイナリ文字列です。これらのプロパティーがあります。
- WEIGHT_STRING(str1) = WEIGHT_STRING(str2) の場合は、str1 = str2 です (str1 と str2 は等しいとみなされます)。
- WEIGHT_STRING(str1) < WEIGHT_STRING(str2) の場合は、str1 < str2 です (str1 は str2 の前にソートされます)。
WEIGHT_STRING() は、照合順序のテストおよびデバッグを行う際、特に新しい照合順序を追加する場合に使用できます。セクション10.4「文字セットへの照合順序の追加」を参照してください。

入力文字列 str は文字列式です。入力が非バイナリ (文字) 文字列 (CHAR、VARCHAR、TEXT 値など) である場合は、戻り値に文字列の照合順序重みが含まれます。入力がバイナリ (バイト) 文字列 (BINARY、VARBINARY、BLOB 値など) である場合は、戻り値は入力と同じです (バイナリ文字列のバイトごとの重みはバイト値です)。入力が NULL である場合は、WEIGHT_STRING() は NULL を返します。

例:
```
mysql> SET @s = _latin1 'AB' COLLATE latin1_swedish_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 4142                   |
+------+---------+------------------------+
```
```
mysql> SET @s = _latin1 'ab' COLLATE latin1_swedish_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 4142                   |
+------+---------+------------------------+
```
```
mysql> SET @s = CAST('AB' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 4142                   |
+------+---------+------------------------+
```
```
mysql> SET @s = CAST('ab' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 6162                   |
+------+---------+------------------------+
```
上記の例では、HEX() を使用して WEIGHT_STRING() の結果を表示しています。結果はバイナリ値であるため、結果に出力されない値が含まれるときに出力可能な形式で表示する際に、HEX() が特に役立ちます。
```
mysql> SET @s = CONVERT(0xC39F USING utf8) COLLATE utf8_czech_ci;
mysql> SELECT HEX(WEIGHT_STRING(@s));
+------------------------+
| HEX(WEIGHT_STRING(@s)) |
+------------------------+
| 0FEA0FEA               |
+------------------------+
```
非 NULL の戻り値では、長さが VARBINARY の最大長内である場合は、値のデータ型が VARBINARY であり、その他の場合はデータ型は BLOB です。

入力文字列を非バイナリまたはバイナリの文字列にキャストし、強制的に指定した長さになるように、AS 句が指定されている場合があります。
- AS CHAR(N) は、文字列を非バイナリ文字列にキャストし、N 文字の長さになるように空白文字で右側をパディングします。N は少なくとも 1 にする必要があります。N が入力文字列の長さよりも小さい場合は、文字列が N 文字まで切り捨てられます。切り捨てられても警告は発生しません。
- AS BINARY(N) は、文字列がバイナリ文字列にキャストされ、N が (文字単位ではなく) バイト単位で測定され、パディングで (空白文字でななく) 0x00 バイトが使用される点を除いて同様です。
```
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 41422020                            |
+-------------------------------------+
```
```
mysql> SELECT HEX(WEIGHT_STRING('ab' AS BINARY(4)));
+---------------------------------------+
| HEX(WEIGHT_STRING('ab' AS BINARY(4))) |
+---------------------------------------+
| 61620000                              |
+---------------------------------------+
```
戻り値に特定の照合順序レベルに合った重みが含まれるように指定するために、LEVEL 句が指定される場合があります。

カンマで区切られた 1 つ以上の整数のリストとして、またはダッシュで区切られた 2 つの整数の範囲として、LEVEL キーワードの後ろに levels 指定子が指定される場合があります。句読文字の前後の空白の有無は影響しません。

例:
```
LEVEL 1
LEVEL 2, 3, 5
LEVEL 1-3
```
1 よりも低いレベルは 1 として処理されます。入力文字列照合順序の最大値よりも高いレベルは、照合順序の最大値として処理されます。最大値は照合順序ごとに異なりますが、6 よりも大きい値にはなりません。

レベルのリストでは、レベルを小さい順に指定する必要があります。レベルの範囲では、2 番目の数字が 1 番目よりも小さい場合は、1 番目の数字として処理されます (たとえば、4-2 は 4-4 と同じになります)。

LEVEL 句が省略された場合は、MySQL では LEVEL 1 - max であるとみなされます。ここで、max は照合順序の最大レベルです。

LEVEL が (範囲構文ではなく) リスト構文を使用して指定されている場合は、レベルの数字のあとに次の修飾子を指定できます。
- ASC: 変更なしで重みを返します。これはデフォルトです。
- DESC: ビット反転された重みを返します (たとえば、0x78f0 DESC = 0x870f です)。
- REVERSE: 逆順で重み (つまり、最初の文字を最後に、最後の文字を最後にと、逆に並べた文字列の重み) を返します。
例:
```
mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1));
+--------------------------------------+
| HEX(WEIGHT_STRING(0x007fff LEVEL 1)) |
+--------------------------------------+
| 007FFF                               |
+--------------------------------------+
```
```
mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC));
+-------------------------------------------+
| HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC)) |
+-------------------------------------------+
| FF8000                                    |
+-------------------------------------------+
```
```
mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1 REVERSE));
+----------------------------------------------+
| HEX(WEIGHT_STRING(0x007fff LEVEL 1 REVERSE)) |
+----------------------------------------------+
| FF7F00                                       |
+----------------------------------------------+
```
```
mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC REVERSE));
+---------------------------------------------------+
| HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC REVERSE)) |
+---------------------------------------------------+
| 0080FF                                            |
+---------------------------------------------------+
```
現在、flags 句は使用されていません。

PREV HOME UP NEXT

User Comments

Posted by Noam Rathaus on March 15, 2006

The following ORDER BY will sort a column called Host (varchar 255) that contains either a Hostname or IP addresses, whenever the IP address is found it will be sorted not as string but as integers:

ORDER BY
CAST(SUBSTRING(
Host,
1,
LOCATE('.', Host) - 1)
AS UNSIGNED),
CAST(SUBSTRING(
Host,
LOCATE('.', Host) + 1,
LOCATE('.', Host, LOCATE('.', Host) + 1)
- LOCATE('.', Host) - 1)
AS UNSIGNED),
CAST(SUBSTRING(
Host,
LOCATE('.', Host, LOCATE('.', Host) + 1) + 1,
LOCATE('.', Host,
LOCATE('.', Host, LOCATE('.', Host) + 1) + 1)
- LOCATE('.', Host, LOCATE('.', Host) + 1) - 1)
AS UNSIGNED),
CAST(SUBSTRING(
Host,
LOCATE('.', Host, LOCATE('.', Host,
LOCATE('.', Host) + 1) + 1) + 1,
3)
AS UNSIGNED)

Posted by Bob Collins on March 17, 2006

MySQL does not include a function to split a delimited string. Although separated data would normally be split into separate fields within a relation data, spliting such can be useful either during initial data load/validation or where such data is held in a text field.

The following formula can be used to extract the Nth item in a delimited list, in this case the 3rd item "ccccc" in the example comma separated list.

select replace(substring(substring_index('aaa,bbbb,ccccc', ',', 3), length(substring_index('aaa,bbbb,ccccc', ',', 3 - 1)) + 1), ',', '') ITEM3

The above formula does not need the first item to be handled as a special case and returns empty strings correctly when the item count is less than the position requested.

More on this and related matters can be found at http://www.kanolife.com/escape/2006/03/mysql-string-splitter.html

Posted by on March 22, 2006

This will split an IP address ("a.b.c.d") into 4 respective octets:

SELECT
`ip` ,
SUBSTRING_INDEX( `ip` , '.', 1 ) AS a,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', 2 ),'.',-1) AS b,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', -2 ),'.',1) AS c,
SUBSTRING_INDEX( `ip` , '.', -1 ) AS d
FROM log_table

Posted by on March 31, 2006

I found myself wanting a unique list of domain names from a table/column of fully qualified email addresses. There isn't a split function so using the other comments here I've devised this:

SELECT DISTINCT REVERSE(LEFT(REVERSE(email),LOCATE('@',REVERSE(email)) - 1)) AS domain FROM table ORDER BY domain

Reverses email, counts the characters from left minus the @. Reverses the reverse and returns 'domain.com'.

Perhaps there is a better/fast/easier way, however it's not easily found. So here is mine.

Posted by Dan Nelson on April 6, 2006

[name withheld], you could extract the domain in two simpler ways:

SELECT SUBSTRING_INDEX(email,'@',-1) AS domain FROM TABLE -- returns everything to the right of the rightmost @

SELECT SUBSTRING(email,INSTR(email,'@')+1) AS domain FROM TABLE -- returns everything to the right of the leftmost @

Both will return identical results on email addresses, since they only have one @ in them. I can't believe you didn't think of SUBSTRING_INDEX, even after the previous two comments used it :)

Posted by Tom O'Malley on April 17, 2006

An example of how to make the first letter in a string uppercase - analogous to UCFIRST

SELECT CONCAT(UPPER(SUBSTRING(firstName, 1, 1)), LOWER(SUBSTRING(firstName FROM 2))) AS properFirstName

Posted by Martin Krsek on May 16, 2006

CONCAT_WS(' AND ', NULL, NULL)
returns empty string instead of NULL, so

SET @where_cond = CONCAT_WS(' AND ', @where1, @where2);
SET @sql = CONCAT_WS(' WHERE ', 'SELECT * FROM table', @where_cond);
SELECT @sql;
results in
SELECT * FROM table WHERE
if both @where1 and @where2 are NULL

Posted by on May 20, 2006

For folks trying to lookup Countries associated with IPs (as in, e.g., databases found here: http://ip-to-country.webhosting.info/node/view/6), this should do the trick, building on an earlier contribution to this page (Assumes your IP is called "RemoteAddress"):

select RemoteAddress as IP,
( SUBSTRING_INDEX( RemoteAddress, '.', 1 ) * 16777216 +
SUBSTRING_INDEX(SUBSTRING_INDEX( RemoteAddress, '.', 2 ),'.',-1) * 65536 +
SUBSTRING_INDEX(SUBSTRING_INDEX( RemoteAddress, '.', -2 ),'.',1) * 256 +
SUBSTRING_INDEX( RemoteAddress, '.', -1 )
) AS IP2Num
FROM log
;

Posted by Erel Segal on July 24, 2006

Here is a trick to create a simple horizontal graph:

SELECT ColName, EXPORT_SET(pow(2,round(ColName))-1,'+','-','',70) FROM TableName

This will create an area filled with "+", where the length of each "+" bar equals the number in column ColName in that row.

70 is an upper bound on the values in ColName; change it to match your actual data.

Posted by Erel Segal on July 24, 2006

Correction to the previous tip: in the current version, EXPORT_SET does not create a string with more than 64 chars, even if you explicitly ask for 70 chars.

Another problem is that for numbers N > 53, 2^N - 1 equals 2^N because of rounding errors, so you will not see a bar, only a single "+".

Posted by Andrew Hanna on August 24, 2006

I created a user-defined function in MySQL 5.0+ similar to PHP's substr_count(), since I could not find an equivalent native function in MySQL. (If there is one please tell me!!!)

delimiter ||
DROP FUNCTION IF EXISTS substrCount||
CREATE FUNCTION substrCount(s VARCHAR(255), ss VARCHAR(255)) RETURNS TINYINT(3) UNSIGNED LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE count TINYINT(3) UNSIGNED;
DECLARE offset TINYINT(3) UNSIGNED;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;

SET count = 0;
SET offset = 1;

REPEAT
IF NOT ISNULL(s) AND offset > 0 THEN
SET offset = LOCATE(ss, s, offset);
IF offset > 0 THEN
SET count = count + 1;
SET offset = offset + 1;
END IF;
END IF;
UNTIL ISNULL(s) OR offset = 0 END REPEAT;

RETURN count;
END;

||

delimiter ;

Use like this:

SELECT substrCount('/this/is/a/path', '/') `count`;

`count` would return 4 in this case. Can be used in such cases where you might want to find the "depth" of a path, or for many other uses.

Posted by Michael Newton on August 31, 2006

To [name withheld] who suggested a method for turning IP addresses into numbers, I would suggest that the INET_ATON() function is a little easier to use!

Posted by NOT_FOUND NOT_FOUND on August 21, 2008

It's pretty easy to create your own string functions for many examples listed here

## Count substrings

CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
return (length(x)-length(REPLACE(x, delim, '')))/length(delim);

SELECT substrCount('/this/is/a/path', '/') as count;

+-------+
| count |
+-------+
|     4 |
+-------+

SELECT substrCount('/this/is/a/path', 'is') as count;

+-------+
| count |
+-------+
|     2 |
+-------+

## Split delimited strings

CREATE FUNCTION strSplit(x varchar(255), delim varchar(12), pos int) returns varchar(255)
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');

select strSplit("aaa,b,cc,d", ',', 2) as second;

+--------+
| second |
+--------+
| b      |
+--------+

select strSplit("a|bb|ccc|dd", '|', 3) as third;

+-------+
| third |
+-------+
| ccc   |
+-------+

select strSplit("aaa,b,cc,d", ',', 7) as 7th;

+------+
| 7th  |
+------+
| NULL |
+------+

## Upper case first letter, UCFIRST or INITCAP

CREATE FUNCTION ucfirst(x varchar(255)) returns varchar(255)
return concat( upper(substring(x,1,1)),lower(substring(x,2)) );

select ucfirst("TEST");

+-----------------+
| ucfirst("TEST") |
+-----------------+
| Test            |
+-----------------+

##Or a more complicated example, this will repeat an insert after every nth position.

drop function insert2;
DELIMITER //
CREATE FUNCTION insert2(str text, pos int, delimit varchar(124))
RETURNS text
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE str_len INT;
DECLARE out_str text default '';
SET str_len=length(str);
WHILE(i<str_len) DO
SET out_str=CONCAT(out_str, SUBSTR(str, i,pos), delimit);
SET i=i+pos;
END WHILE;
-- trim delimiter from end of string
SET out_str=TRIM(trailing delimit from out_str);
RETURN(out_str);
END//
DELIMITER ;

select insert2("ATGCATACAGTTATTTGA", 3, " ") as seq2;

+-------------------------+
| seq2                    |
+-------------------------+
| ATG CAT ACA GTT ATT TGA |
+-------------------------+

Posted by w pociengel on October 20, 2006

I was trying to output a text message that told me if a field was null. I tried various mechanisms but this proved to do the trick.

elt(((field1 <=> NULL) + 1),"not null", "null")

evaluating (field1 <=> NULL) returns 0 (zero) if the field is not null and 1 (one) if the field is null. Adding 1 (one) to this result provides positional information that fits what 'elt' expects.

elt will return "not null" (position 1) if the evaluation of ((field1 <=> NULL) + 1) = 1

it will return "null" (position 2) if the evaluation of ((field1 <=> NULL) + 1) = 2

This can be altered to output messages based on any test that I've tried. Just remember that 'elt' returns null or 1 for a comparison so you need to add 1 (one) to that result to be able to choose between different messages.

Posted by J Vera on October 26, 2006

As above I couldn't find a function for splitting strings based on a character set rather than string position, where the results were independent of substring lengths. I used this query to split out the Swiss-Prot accession numbers from BLAST result subject ID's, which are bracketed by pipe ('|') characters, but any two relatively unique characters should work.

select left(substring(<columnName>,locate('|',<columnName>)+1),
locate('|',substring(<columnName>,
locate('|',<columnName>)+1))-1)
as '<resultColumnName>' from <table>

Posted by Giovanni Campagnoli on December 20, 2006

This is the php function strip_tags

delimiter ||

DROP FUNCTION IF EXISTS strip_tags||
CREATE FUNCTION strip_tags( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE sstart INT UNSIGNED;
DECLARE ends INT UNSIGNED;
SET sstart = LOCATE('<', x, 1);
REPEAT
SET ends = LOCATE('>', x, sstart);
SET x = CONCAT(SUBSTRING( x, 1 ,sstart -1) ,SUBSTRING(x, ends +1 )) ;
SET sstart = LOCATE('<', x, 1);
UNTIL sstart < 1 END REPEAT;
return x;
END;
||
delimiter ;

mysql> SELECT strip_tags('<a href="HelloWorld.html"><B>Hi, mate!</B></a>') as strip_tags;

+------------+
| strip_tags |
+------------+
| Hi, mate!  |
+------------+

1 row in set (0.00 sec)

Posted by Stephen Gornick on December 24, 2006

If using statement-based replication, load_file() will fail on the slave unless the same file exists on the slave as on the master.

Posted by Nicola De Franceschi on January 16, 2007

Here's my formula to remove a value from a string field of comma separated values. You can use a different delimiter just repalce the comma in the formula but pay attention since lot of commas here are the argument separator of the used functions.
The nice part is that with this formula you don't need to distinguish the first and last element of the string and this formula removes just the value_to_remove so if your initial string is: "4,11,34" and the value you want to remove is "4" you'll get "11,34".

UPDATE temp SET string = TRIM(BOTH ',' FROM REPLACE(CONCAT("," , string, ","), CONCAT(",",'value_to_remove', ",") , ',')) WHERE id=1

Posted by Robert Glover on February 13, 2007

There is a simple way to convert the following Oracle usage of decode into MySql:

Oracle version:

select BU, count(line_number) total,
sum(decode(RECERTIFY_FLAG,'Y',1,0)) needed,
sum(decode(RECERTIFY_FLAG,'N',1,0)) not_needed,
sum(decode(RECERTIFY_FLAG,'Y',0,'N',0,1)) not_processed
from isf.isf_analog_line group by bu order by bu

MySql version that gives same results:

select BU, count(line_number) total,
sum(FIND_IN_SET(RECERTIFY_FLAG,'Y')) needed,
sum(FIND_IN_SET(RECERTIFY_FLAG,'N')) not_needed,
sum(FIND_IN_SET(RECERTIFY_FLAG,' ')) not_processed
from isf.isf_analog_line group by bu order by bu

Add your own comment.

Posted by Balaji Devarajan on March 8, 2007

I was looking for word_count("string") in mysql, finally came up with an user defined function which is very usefull for me, note: I used <space> for actual space.
DROP FUNCTION IF EXISTS word_count;
CREATE FUNCTION word_count (f_string text(5000)) RETURNS smallint(10)
BEGIN
DECLARE new_string text(5000);
WHILE INSTR(f_string,'<space><space>')>0
DO
SET new_string=(select REPLACE(f_string,'<space><space>','<space>'));
SET f_string=new_string;
END WHILE;

RETURN (select LENGTH(TRIM(f_string))-LENGTH(REPLACE(TRIM(f_string),'<space>',''))+1);
END
//
Here is the result
mysql> select word_count("<space>Balaji<space><space><space> Devarajan<space>") WORD_COUNT;

+------------+
| WORD_COUNT |
+------------+
|          2 |
+------------+

1 row in set (0.00 sec)
mysql> select word_count(" Balaji Devarajan ") WORD_COUNT;

+------------+
| WORD_COUNT |
+------------+
|          2 |
+------------+

1 row in set (0.00 sec)
mysql> select word_count("Balaji Devarajan") WORD_COUNT;

+------------+
| WORD_COUNT |
+------------+
|          2 |
+------------+

1 row in set (0.01 sec)

Posted by Balaji Devarajan on March 8, 2007

Here is another function I wrote, which is very usefull in getting the domain name from the url, please bare with the spaces in the http : //, if not iam not able to submit this with many urls
delimiter //
drop function if exists sub_domain;
CREATE FUNCTION sub_domain (url text(1000)) RETURNS CHAR(50)
BEGIN
DECLARE str1 varchar(10);
DECLARE str2 varchar(10);
SET str1=http : //;
SET str2=https://;
RETURN if(substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-1) != 'com',substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-3),substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-2));
END;
//
mysql> select domain("http : //maps.google.com/maps?hl=en&q=what%20is%20domain%20name&btnG=Google+Search&ie=UTF-8&oe=UTF-8&um=1&sa=N&tab=il") DOMAIN;

+------------+
| DOMAIN     |
+------------+
| google.com | 
+------------+

1 row in set (0.00 sec)
similarly we can get the sub-domain too.
delimiter //
drop function if exists sub_domain;
CREATE FUNCTION sub_domain (url text(1000)) RETURNS CHAR(50)
BEGIN
DECLARE str1 varchar(10);
DECLARE str2 varchar(10);
SET str1=http : //;
SET str2=https : //;
RETURN if(substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-1) != 'com',substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-3),substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-3));
END;
//
mysql> select sub_domain("http : //maps.google.com/maps?hl=en&q=what%20is%20domain%20name&btnG=Google+Search&ie=UTF-8&oe=UTF-8&um=1&sa=N&tab=il") sub_domain;

+-----------------+
| sub_domain      |
+-----------------+
| maps.google.com | 
+-----------------+

1 row in set (0.00 sec)

Posted by Balaji Devarajan on March 24, 2007

I was looking for function, to give me the MOST COMMON VALUE IN A STRING. Used Java or php to do this, using substrCount and SplitString (thanks to Chris Stubben). I got the below function, which will give me the mcv value.

DELIMITER //
DROP FUNCTION IF EXISTS get_mcv;
CREATE FUNCTION get_mcv (list text(10000)) RETURNS text(1000)
BEGIN
DECLARE cnt int(10);
DECLARE iter_cnt int(10);
DECLARE item text(100);
DECLARE f_item text(100);
DECLARE prv_cnt int(10) default 0;
DECLARE nxt_cnt int(10) default 0;

IF list=' ' THEN
RETURN list;
END IF;

select substrCount(list,',')+1 into cnt;
SET iter_cnt = 1;

while cnt >= iter_cnt
do
select charsplit(list,',',iter_cnt) into item;

select substrCount(concat(',',list,','),concat(',',item,',')) into nxt_cnt;
IF nxt_cnt > prv_cnt THEN
SET prv_cnt = nxt_cnt;
SET f_item = item;
END IF;

set iter_cnt=iter_cnt+1;
end while;
RETURN f_item;

END
//

mysql> select get_mcv("dsfds,dsfds,fdfds,dfdsf,sd,df,df,df,df");

+---------------------------------------------------+
| get_mcv("dsfds,dsfds,fdfds,dfdsf,sd,df,df,df,df") |
+---------------------------------------------------+
| df                                                | 
+---------------------------------------------------+

1 row in set (0.02 sec)

Posted by S D on March 28, 2007

A field may contain delimited values that may be used with 'IN' operator in a where clause. However each of the values need to be nested within single quote(').
This function uses stringSplit and substrCount - thanks to Chris Stubben.
CREATE FUNCTION cs2in(x varchar(255), delim varchar(12)) returns varchar(255) deterministic
BEGIN
DECLARE retstr varchar(255);
DECLARE Valcount INT(10);
DECLARE v1 INT(10);
SET retstr = '';
SET Valcount = substrCount(x,delim)+1;
SET v1=0;
WHILE (v1 < Valcount) DO
SET retstr = concat_ws(',',retstr,quote(stringSplit(x,delim,v1+1)));
SET v1 = v1 + 1;
END WHILE;
SET retstr = CONCAT('(',TRIM(LEADING ',' FROM TRIM(retstr)),')');
RETURN retstr;
END

E.g.
mysql> Select cs2in('1,2,3,4,5',',') as IN_format;

+---------------------+
| IN_format           |
+---------------------+
|('1','2','3','4','5')|
+---------------------+

This format is compatible for use in the 'IN' clause.

Posted by LEO DIVINAGRACIA on June 14, 2007

for a DIFFERENCE like function when comparing two strings together, try this:

------------------------------

DELIMITER $$

DROP FUNCTION IF EXISTS `db2`.`diff3`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `diff3`(n1 varchar(50), n2 varchar(50)) RETURNS int(11)
DETERMINISTIC
BEGIN
declare s1 char(1);
declare s2 char(1);
declare s3 int;
declare s4 int;
declare l1 int;
declare diff int;
set l1 = 1;
set diff = 0;
repeat
set s1 = substring(soundex(n1),l1,1);
set s2 = substring(soundex(n2),l1,1);
if s1 = s2 then set diff = diff + 1;
end if;
set l1 = l1 + 1;

until l1 > 4
end repeat;
return diff;
END$$

DELIMITER ;

----------------------

other DBMS have this function and i kinda needed one. so looked and mysql's online docs shows a DIFFERENCE function but that was for GIS apps and isnt current implemented.

just change the "user@hostname" and the "db.function_name" to reflect your info.

returns an INT value from 0 to 4, where 0 means the SOUNDEX of each string doesnt have any same value. 4 means each 4 alphanumeric digit is the same:

select soundex("hello"), soundex("jello")

returns

H400 and J400

so DIFF3("hello", "jello")

returns a 3

while DIFF3("hello","great")

returns a 1

as a stored function, you can do something like:

select firstname from mytable where diff3("joe bob", firstname) > 3

Posted by Brett Millett on June 29, 2007

Just some syntax to pass along for those who may wanna reformat credit cards (if you are brave enough to store them as plain text in the first place) before sending them to an application for displaying. This lets you pad out all the numbers except the last four (all you developers have done this millions of times I'm sure.)

SELECT LPAD(SUBSTRING(`cardnumbercolumn`, -4, 4),LENGTH(`cardnumbercolumn`),'*') FROM table

Posted by Nigel Forward on July 6, 2007

To format a number with leading zeros you can use LPAD:

eg: LPAD(13, 4, '0') returns '0013'

Any numbers that generate more than the number of digits (4 in this case) would be truncated from the left:

eg: LPAD(41278, 4, '0') returns '1278'

Posted by ben j on September 4, 2007

Simple Split
SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( 'a|b|c|d|e|f|g|h', '|', index), '|', -1 );

Posted by Andrej Abehtikov on September 19, 2007

A simple way to convert IP address from string '0A0B0C0D' into 10.11.12.13 :
CONCAT(
ASCII(UNHEX(SUBSTRING(yourIP,1,2))), '.',
ASCII(UNHEX(SUBSTRING(yourIP,3,2))), '.',
ASCII(UNHEX(SUBSTRING(yourIP,5,2))), '.',
ASCII(UNHEX(SUBSTRING(yourIP,7,2)))
)

Posted by Steven Benjamin on November 12, 2007

I sometimes need to find the last occurrence of a string. Using a previous post I did this by implementing the following procedure

delimiter ||
DROP FUNCTION IF EXISTS locatelast||
CREATE FUNCTION locatelast(s VARCHAR(1000), ss VARCHAR(1000)) RETURNS TINYINT(3) UNSIGNED LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE last TINYINT(3) UNSIGNED;
DECLARE offset TINYINT(3) UNSIGNED;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;

SET last = 0;
SET offset = 1;

REPEAT
IF NOT ISNULL(s) AND offset > 0 THEN
SET offset = LOCATE(ss, s, offset);
IF offset > 0 THEN
SET last = offset;
SET offset = offset + 1;
END IF;
END IF;
UNTIL ISNULL(s) OR offset = 0 END REPEAT;

RETURN last;
END;

Posted by Denis Roy on November 15, 2007

In response to Steven Benjamin on November 12 2007 7:53pm

Instead of looping through the string to look for the last occurrence, simply reverse() the string and look for the first occurrence, then substract the found position from the string length:

select @loc:=length(realname) - locate(" ", reverse(realname)) from table

For instance, looking for the last occurrence of a space?

select @string:="A horse with no name" as String, @loc:=length(@string) - locate(" ", reverse(@string))+1 AS lastoccurrence, left(@string, @loc), substr(@string,@loc+1);

+----------------------+----------------+---------------------+------------------------+
| String               | lastoccurrence | left(@string, @loc) | substr(@string,@loc+1) |
+----------------------+----------------+---------------------+------------------------+
| A horse with no name |             16 | A horse with no     | name                   |
+----------------------+----------------+---------------------+------------------------+

Posted by Matias Alejo Garcia on November 28, 2007

It is important to check the value of group_concat_max_len
where using GROUP_CONCAT.

The default is 8192 (bytes), and if the result is bigger, it will be silently cropped, leading to unexpected results.
Some examples here: http://confronte.com.ar/groupconcat

Posted by Axel Axel on December 14, 2007

If you want to compare an empty string to a numeric value or an integer field, you'll have to CAST the integer field or value to a string, due to the fact that for mysql, a zero (the integer one) equals to an empty string

Example :
SELECT 0 = '';
==> 1

SELECT '0' = '';
==> 0

SELECT CAST(0 AS CHAR) = '';
==> 0

This is common when you want to check user input : if a user inputs a "0" for a field, the check without cast will fail because mysql thinks this is an empty string.

Posted by Grigory Dmitrenko on December 21, 2007

Just checked a lot of documentation and also didn't find any solution to split delimited string into an array (temporary table).
I know the example where it is needed to walk through the input string to find dilimiters but I think this way annoying.
You may use my solution from below which is also keeping the order of substring occurance:

(input string is stored in "input" variable)

CREATE TEMPORARY TABLE temp (id TINYINT NOT NULL auto_increment, val CHAR(20), PRIMARY KEY(id));
SET input=REPLACE(input, ",", "'),('");
SET @dyn_sql=CONCAT("INSERT INTO temp (val) VALUES ('",input,"');");
PREPARE s1 FROM @dyn_sql; EXECUTE s1;
SELECT * FROM temp;

Hope this would help someone :)

Posted by robyn wyrick on February 29, 2008

In response to Denis Roy on November 15 2007 8:46pm

That is a great example. Here is how I used a very similar example to find a contact's last name from a contacts database by sub string on the last instance of the space:

SELECT @string:=Full_Name, SUBSTRING(@string, LENGTH(SUBSTRING(REVERSE(@string), LOCATE(" ", REVERSE(@string))-1))) AS Last_Name FROM contacts;

Something that would more elegant would be to have the LOCATE function include a direction option, like:

SELECT SUBSTRING(foo, LOCATE(" ", foo, RIGHT)) AS bar;

Posted by Lu Yizhen on April 16, 2008

if you want to remove the tab(\t)
try this
update temp set locus=trim(BOTH ' ' from locus)

Posted by horace borov on April 30, 2008

dudes & dudettes,

if you want to find the last occurrence of a particular string, use the tools mysql provides for you:

select reverse( substring( reverse( field ), locate( 'xyz', reverse( field ) )+1 ) )

---
this is way easier to implement and debug

Posted by none dotcom on May 21, 2008

As i was in charge to support soundex compatible string comparison i had made following first mysql function.
Maybe it will help someone to find it's own and better solutions ;)

CREATE FUNCTION SOUNDEX_SEARCHTEXT( haystack LONGTEXT, needle VARCHAR(40) ) RETURNS INT
BEGIN
DECLARE part VARCHAR(40) DEFAULT SUBSTRING( haystack, 1, LENGTH(needle) );
DECLARE iterator INT DEFAULT 1;
WHILE iterator < LENGTH( haystack )-LENGTH(needle)+1 DO
SET iterator = iterator + 1;
SET part = SUBSTRING( haystack, iterator, LENGTH(needle) );
IF part SOUNDS LIKE needle THEN
RETURN 1;
END IF;
END WHILE;
RETURN 0;
END

Hope it will be usefull - with best regards

Posted by Mesha Kumar on June 6, 2008

I was going thru the example given in earlier post about the INITCAP/UCFIRST fucnctionality. It was very useful for the strings without space.. below procedure would useful for those string having space in it.

DELIMITER $$
DROP FUNCTION IF EXISTS `initcap`$$
CREATE DEFINER=`root`@`%` FUNCTION `initcap`(x varchar(255)) RETURNS varchar(255) CHARSET utf8
begin
set @l_str='';
set @r_str='';

if x REGEXP ' ' then
SELECT SUBSTRING_INDEX(x, ' ',1) into @l_str;
SELECT SUBSTRING_INDEX(x, ' ',-1) into @r_str;
return concat(concat( upper(substring(@l_str,1,1)),lower(substring(@l_str,2))),' ',concat( upper(substring(@r_str,1,1)),lower(substring(@r_str,2))));

else

return concat( upper(substring(x,1,1)),lower(substring(x,2)));

end if;

end$$
DELIMITER ;

Posted by YiXia SUN on August 26, 2008

In response to robyn wyrick on February 29 2008 7:01pm and Denis Roy on November 15 2007 8:46pm

Thank you for the hint!

Follow your examples I generate the following statement:
RIGHT(foo, locate(' ', REVERSE(foo))-1) as foo

Posted by Alex Vekselman on September 7, 2008

Hello,

There are several stored procedures (e.g., see post of Grigory Dmitrenko) to transform
string like 'a,b,c' into something to be used like
....WHERE x IN ('a','b','c').

Here is solution to transform delimited string
into real table. Thus, it can be used further in
JOIN or IN constructs.

Pre-condition -- existence of some table with row count
bigger than elements in string. I will use
3-row table created on-fly by UNION.

mysql> select substring_index(substring_index('a,b,c', ',',@r:=@r+1),',',-1) zxz
from (select @r:=0) x,
(select 'x' xx union select 'v' xx union select 'z' xx) z;

+------+
| zxz |
+------+
| a |
| b |
| c |
+------+

3 rows in set (0.00 sec)

Thanks ben j for double *substring_index* example posted above.

Posted by Luke Burgess on October 31, 2008

Chop off last 3 characters from a string. Cannot find an official way of doing this! This method works:

SELECT SUBSTRING(foobar,1,LENGTH(foobar)-3) AS foobar

Posted by Michael Lisicki on November 16, 2008

I find this handy to extract the domain name (including subdomain) from an URL:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(url,'://',-1),'/',1)
FROM urls

It works for URLs with and without http(s). But doesn't work if you have local URLs without a leading slash like "folder/index.html". In that case it extracts "folder" instead of an empty string.

Posted by Ilde Giron on November 28, 2008

I recently found that after filling a table with info from a csv file created with MS Excel, an unwanted character went into the end of a field, and it showed up as "^M". So, when I issued a
mysql> select description from catalog;

the list looked all garbled

I used next command to remove it (most, but not all of the rows in the table were contaminated):
mysql> update catalog set description = left(description,length(description) -1) where description like "%^M%";

Please note that to replicate that "^M" you must press <ctrl> and v --though no character will be displayed-- and then <ctrl> and m.

Posted by Steve Klein on February 16, 2009

You can use REVERSE to parse the last token from a string. This can be useful for name processing, for instance (first name is everything except last token and last name is last token):

SELECT
REVERSE(SUBSTR(REVERSE(name),INSTR(REVERSE(name),' ')+1)) AS first,
REVERSE(SUBSTR(REVERSE(name),1,INSTR(REVERSE(name),' '))) AS last
FROM table

Posted by Phil Barone on March 6, 2009

Thanks Ilde for sharing your <control><m> example and especially how to type the <control><m> into the console.

One minor change, since you are only replacing the one character at the end is, change the where clause to

where description like "%^M";

Posted by Matt Cavanagh on May 15, 2009

Was looking around trying to find out how to use the initcap functionality that Oracle has but mysql does not and came across the two suggestions posted above. The second example, however, is still not 100% accurate, as if there are more than 2 words in the data, it will capitalize the first and last words, and completely delete anything in between! I wanted to write a full version of this method:

-- ***********************************

DELIMITER $$
DROP FUNCTION IF EXISTS `initcap` $$
CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8
BEGIN
SET @str='';
SET @l_str='';
WHILE x REGEXP ' ' DO
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END $$
DELIMITER ;

-- ***********************************

One gotcha to note: this method strips out any leading and trailing spaces from the input, which really isn't that big of a deal, but something to keep in mind.

Posted by Ed Anderson on May 28, 2009

The ^M character is the DOS EOL character - and you can avoid the entire problem by dumping the file from Excel to a CSV file - if you're running in UNIX/Linux you can use the "dos2unix" utility which will strip out the ^M's and leave you with a portable file. Just my two cents.

Posted by Kim TongHyun on July 23, 2009

I modified the function strSplit(from Chris Stubben) for utf8.

DROP FUNCTION IF EXISTS strSplit;
CREATE FUNCTION strSplit(x varchar(21845), delim varchar(255), pos int) returns varchar(255)
return replace(
replace(
substring_index(x, delim, pos),
substring_index(x, delim, pos - 1),
''
),
delim,
''
);

mysql> select strSplit("あ|ええ|いいい|おおお", '|', 4) as 4th;

+-----------+
| 4th       |
+-----------+
| おおお    | 
+-----------+

Posted by Chris Porter on August 28, 2009

re: to get the element at position x in a string..

instead of using the command:

select replace(
substring_index(field, delim, pos),
substring_index(field, delim, pos - 1),
'')

from table;

you can shorten it to require less modifiying by doing:

select substring_index(
substring_index(field, 'xyz', pos)
, 'xyz', -1)
from table;

that will get the last element of the list of x that were found. which should be the one you want.

hope this helps!

Posted by Arlyn Johns on November 19, 2009

Here is another example of how to transform a comma separated string into a list of values that can be used in a WHERE x IN ('a','b','c') clause:

SELECT * from foo
WHERE FIND_IN_SET(col, REPLACE('a, b, c', ' ', '')) != 0;

Posted by Orhan Dogan on November 21, 2009

count of '@' char:

SELECT
stringfield,
LENGTH(stringfield)-LENGTH(REPLACE(stringfield,'@',''))
FROM tablename

Posted by Ernesto Spiro Peimbert Andreakis on December 29, 2009

Here is a function I created to simulate PHP's ucwords, I know it can be improved so you're welcome to do so:

CREATE FUNCTION `ucwords`(`string` text) RETURNS text CHARSET latin1
BEGIN
DECLARE final_string text default '';
DECLARE char_index int unsigned default 1;
DECLARE upperchar int unsigned default 1;

IF LENGTH(string)>0 THEN
IF LOCATE(' ',string) > 0 OR LOCATE('.',string) OR LOCATE('(',string) > 0 OR LOCATE('¿',string) THEN
REPEAT
IF upperchar = 1 THEN
SET final_string = CONCAT(final_string,UPPER(SUBSTRING(string,char_index,1)));
SET upperchar = 0;
ELSE
SET final_string = CONCAT(final_string,SUBSTRING(string,char_index,1));
END IF;
IF (SUBSTRING(string,char_index,1) = ' ') OR (SUBSTRING(string,char_index,1) = '.') OR (SUBSTRING(string,char_index,1) = '(') OR (SUBSTRING(string,char_index,1) = '¿') THEN
SET upperchar = 1;
END IF;
SET char_index = char_index + 1;
UNTIL char_index > LENGTH(string)
END REPEAT;
ELSE
SET final_string = CONCAT(UPPER(SUBSTRING(string,1,1)),SUBSTRING(string,2));
END IF;
ELSE
SET final_string = string;
END IF;
RETURN final_string;
END

Posted by Claude Warren on February 10, 2010

I needed a way to parse UTF-8 strings into words, not finding any mechanism that would allow me to specify a list of characters to split on I hit upon using regexp and string manipulation to parse the string. The following is a function to find regex defined positions in a string and a procedure to break words out of a string based on regex.

delimiter $$

--
-- This function will return the first position in p_str where the regexp is true
--
drop function if exists regexPos $$
create function regexPos( p_str TEXT, p_regex varchar(250) ) returns int
BEGIN
declare v_pos int;
declare v_len int;
set v_pos=1;
set v_len=1+char_length( p_str );
while (( substr( p_str, 1, v_pos) REGEXP p_regex)=0 and (v_pos<v_len))
do
set v_pos = v_pos + 1;
end while;
return v_pos-1;
end $$

--
-- This procedure parses p_str into words based on the regular expression p_regex.
-- The simplest usage is call ParseWords( "some string", "[[:space:]]" );
-- this will break the string on spaces.
CREATE procedure ParseWords (IN p_str TEXT, IN p_regex varchar(256))
begin
declare v_startPos int;
declare v_strLen int;
declare v_wordLen int;
set v_startPos=1;
set v_strLen=char_length( p_str )+1;

while ( v_startPos < v_strLen )
do
set v_wordLen = regexPos( substring( p_str, v_startPos ), p_regex );
while (v_wordLen = 0) and ( v_startPos < v_strLen)
do
-- if the wordLen is 0 (zero) then we have a space at the start
-- so remove it and try again.
set v_startPos = v_startPos+1;
set v_wordLen = regexPos( substring( p_str, v_startPos ), p_regex );
end while;
if (v_wordLen > 0)
then
-- we found a word.
-- do something useful here. This example simply prints out the words
-- a real application will probably insert them into a table.
select substring( p_str, v_startPos, v_wordLen );
set v_startPos = v_startPos + v_wordLen +1;
end if;
end while;
end $$

delimiter ;

Posted by Karl Walker on April 9, 2010

I was in need of a way to find the start position of a regex match in a string. I tried using regexPos above, but it seemed to be returning the end position of the match (minus 1). So, I modified it and called it rendpos, and used it to find the start position.

DELIMITER $$

DROP FUNCTION IF EXISTS `rendpos` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `rendpos`(p_regex varchar(250),p_str TEXT) RETURNS int(11)
BEGIN

declare v_endpos int;
declare v_startpos int;
declare v_len int;

set v_endpos=1;
set v_len=1+char_length( p_str );
while (( substr( p_str, 1, v_endpos) REGEXP p_regex)=0 and (v_endpos<v_len))
do
set v_endpos = v_endpos + 1;
end while;

return v_endpos;

END $$

DELIMITER ;

Here is a quick and dirty find of start position. It will find the minimal match instead of the maximal pattern match. Please feel free to modify this to find the maximal pattern match.

DELIMITER $$

DROP FUNCTION IF EXISTS `rlocate` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `rlocate`(p_regex varchar(250),p_str TEXT, s_startpos int) RETURNS int(11)
BEGIN
declare v_endpos int;
declare v_startpos int;
declare v_len int;

set v_endpos=rendpos(p_regex, p_str, s_startpos);

set v_startpos = v_endpos;
while (( substr( p_str, v_startpos, v_endpos) REGEXP p_regex)=0 and (v_startpos > 0))
do
set v_startpos = v_startpos - 1;
end while;

return v_startpos;
END $$

DELIMITER ;

The extract uses the above two functions, so it will likewise extract the minimal pattern.

DELIMITER $$

DROP FUNCTION IF EXISTS `rextract` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `rextract`(p_regex varchar(250),p_str TEXT, s_startpos int) RETURNS varchar(250) CHARSET latin1
begin

declare startpos int;
declare endpos int;

set startpos = rlocate(p_regex,p_str,s_startpos);
set endpos = rendpos(p_regex,p_str,s_startpos);

return mid(p_str,startpos,endpos-startpos+1);

end $$

DELIMITER ;

Posted by Asle Benoni on July 9, 2010

To update a text field and change all city names to Titlecase (propercase), this short code works:

UPDATE mytable SET city = CONCAT(UCASE(MID(city,1,1)),MID(LCASE(city),2))

So city names PARIS or paris will be changed to Paris.

Posted by Carsten Meier on July 26, 2010

FIELD() doesn't work with multiple columns, e.g. FIELD((col1, col2), (11,12), (22,23)) aborts with error #1241.

To work around this limitation, one can use nested IF() functions like this:

IF((col1, col2)=(11,12), 1, IF((col1, col2)=(22,23), 2, 0))

Posted by Mike Fehse on September 25, 2010

To expand a word, from an abbreviation to the full spelling, such as 'Ing:' to 'Ingredient(s):', or just inserting something without overwriting everything else, you can use the following:

UPDATE table_name AS t SET t.field_name = INSERT(t.field_name, 4, 0, 'redient(s)') WHERE LEFT(t.field_name, 4) = 'Ing:';

This inserts 'edient(s)' between the 'g' and ':' in 'Ing:', giving us 'Ingredient(s):' It also tests t.field_name to see if it is to be updated. This is based on knowing that the first three charters in t.field_name will be 'Ing:' or not, and if it is then we spell it out. You can even expand the spelling in the t.field_name, not just the start or end of it, as this might suggest. Use INSTR(t.field_name, 'str_to_expand'), so it would end up looking like:

UPDATE table_name AS t SET t.field_name = INSERT(t.field_name, INSTR(t.field_name, 'Ing:'), 0, 'redient(s)') WHERE LEFT(t.field_name, 4) = 'Ing:';

If you know that you have the abbreviation in more than just one place within a field (aka column) then just run the command again. In both cases the number zero '0' is the key, it tells the INSERT command not to overwrite any of the following charters, just insert the requested sub-string.

Posted by Benjamin Bouraada on October 27, 2010

Stringcutting:

PROCEDURE Insert(IN STR_IN VARCHAR(255), IN ID_IN INT)
BEGIN
declare lang int default LENGTH(STR_IN);
declare sep char default ';';
declare tmpMerge char(255);
declare tmpChar char;
declare loop_done integer default 1;
stringcutter: loop
set tmpChar = substring(STR_IN,loop_done,1);
if tmpChar <> sep then
if isnull(tmpMerge) then set tmpMerge = tmpChar;
else select concat(tmpMerge,tmpChar)into tmpMerge;end if;end if;
if tmpChar = sep then DO WHAT YOU WANT;set tmpMerge = NULL;end if;
set loop_done = loop_done + 1;
if loop_done = lang then leave stringcutter;end if;
end loop stringcutter;
END

Posted by Yerkebulan Kuryshbayev on January 24, 2011

my mysql split function:

DROP PROCEDURE IF EXISTS `SPLIT_STRING`;

DELIMITER |
CREATE PROCEDURE `SPLIT_STRING` (IN `MY_STRING` TEXT, IN `MY_DELIMITER` TEXT)
LANGUAGE SQL
SQL SECURITY INVOKER
BEGIN
#-------------------------------------------------------------------------------
IF NOT ISNULL(MY_STRING) THEN
IF NOT ISNULL(MY_DELIMITER) THEN
#
SET @SS = TRIM(MY_STRING);
SET @DEL = TRIM(MY_DELIMITER);
#
IF LENGTH(@SS) > 0 THEN
IF LENGTH(@DEL) > 0 THEN
#
SET @DP = (SELECT LOCATE(@DEL, @SS, 1));
IF @DP > 0 THEN
#------------------------CREATE TEMP TABLE-----------------------
DROP TABLE IF EXISTS `TEMPORARY_TABLE_OF_SPLIT_STRINGS`;
#
CREATE TEMPORARY TABLE `TEMPORARY_TABLE_OF_SPLIT_STRINGS` (
`SUB_STRING` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
)
ENGINE=INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci ;
#----------------------------------------------------------------
SET @SS_2 = @SS;
#
REPEAT
#
SET @FIRST_ELEMENT = (SELECT SUBSTRING_INDEX(@SS_2, @DEL, 1));
SET @SS_2 = (SELECT TRIM(LEADING CONCAT(@FIRST_ELEMENT, @DEL) FROM @SS_2));
#
INSERT INTO `TEMPORARY_TABLE_OF_SPLIT_STRINGS` (`SUB_STRING`) VALUES (@FIRST_ELEMENT);
SET @DP = (SELECT LOCATE(@DEL, @SS, @DP + 1));
#
IF @DP = 0 THEN
SET @LAST_ELEMENT = (SELECT SUBSTRING_INDEX(@SS_2, @DEL, -1));
INSERT INTO `TEMPORARY_TABLE_OF_SPLIT_STRINGS` (`SUB_STRING`) VALUES (@LAST_ELEMENT);
END IF;
UNTIL @DP = 0
END REPEAT;
#
SELECT * FROM TEMPORARY_TABLE_OF_SPLIT_STRINGS;
#----------------------------------------------------------------
DROP TABLE IF EXISTS `TEMPORARY_TABLE_OF_SPLIT_STRINGS`;
#----------------------------------------------------------------
ELSE
SELECT NULL;
END IF;
ELSE
SELECT NULL;
END IF;
ELSE
SELECT NULL;
END IF;
ELSE
SELECT NULL;
END IF;
ELSE
SELECT NULL;
END IF;
END; |
DELIMITER ;

Posted by Juan Andrés Calleja on February 14, 2011

Hi, I found one usefully example in mysql forums (http://lists.mysql.com/mysql/199134) for split a varchar to use in the 'IN' clause in query. I create new input parameter with table name for use in dynamic CREATE table statement.

Thus split function can be used more than once in the same stored procedure because table have any name.

This is the SP. Excuse me for my bad english :(

DELIMITER $$

CREATE PROCEDURE `SplitString`( IN input TEXT,
IN delimiter VARCHAR(10), IN Table_name VARCHAR(50)
)
SQL SECURITY INVOKER
BEGIN
DECLARE cur_position INT DEFAULT 1 ;
DECLARE remainder TEXT;
DECLARE cur_string VARCHAR(1000);
DECLARE delimiter_length TINYINT UNSIGNED;

set @sql_drop = concat('DROP TEMPORARY TABLE IF EXISTS ',' ',Table_name);
prepare st_drop from @sql_drop;
execute st_drop;

set @sql_create = concat('CREATE TEMPORARY TABLE ' ,Table_name ,' (value VARCHAR(1000) NOT NULL PRIMARY KEY) ENGINE=MEMORY;');
prepare st_create from @sql_create;
execute st_create;

SET remainder = input;
SET delimiter_length = CHAR_LENGTH(delimiter);

WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
SET cur_position = INSTR(remainder, delimiter);

IF cur_position = 0 THEN
SET cur_string = remainder;
ELSE
SET cur_string = LEFT(remainder, cur_position - 1);
END IF;

IF TRIM(cur_string) != '' THEN
set @sql_insert = concat('INSERT INTO ' ,Table_name, ' VALUES (',cur_string,');');
prepare st_insert from @sql_insert;
execute st_insert;
END IF;

SET remainder = SUBSTRING(remainder, cur_position +
delimiter_length);
END WHILE;
END

Posted by shiva Kumar on February 17, 2011

Hi mysqlis,

Here the difference between the string functions LOCATE and FIND_IN_SET is..

1.When using LOCATE for integers use carefully..

example:
If I need to return 1 if 2 is in the set '1,2,3,4,5'.

SELECT IF(LOCATE(2,'1,2,3,4,5,6,7,8,9')>0,1,0);
You know very well it return 1,because the set contains value 2 in given set.
SO it is no problem...

FOR this Example query it returns wrong as we expected...

SELECT IF(LOCATE(2,'11,12,3,4,5,6,7,8,9')>0,1,0);
even though 2 is not available in set,it gives 1.

here LOCATE function takes the set as the STRING not the comma(,) separated value..

In this situation Please use the FIND_IN_SET - which is great function for the comma(,) separated value set.

Now,

SELECT IF(FIND_IN_SET(2,'11,12,3,4,5,6,7,8,9')>0,1,0);

It returns 1 as we expected...

Note:
1.Use LOCATE function for alphabetic strings only..
2.And also use LOCATE for numeric numbers that set contains the numbers only 0,1,2,3,4,5,6,7,8,9

i.e.,

SELECT IF(LOCATE(input,'0,1,2,3,4,5,6,7,8,9')>0,1,0);

input must be any one within 0,1,2,3,4,5,6,7,8,9
Its work nicely.

Posted by Vector Thorn on April 9, 2011

Hi guys. For those of you who are trying to do a string replace on a string that contains a wildcard, you can actually write your own UDF to solve that.

Posted by Lies DJILLALI on August 31, 2011

Thank you Giovanni for your strip_tags function,

Here is a patched version because Mysql crashed when I tryied to proceed a NULL value

delimiter ||

DROP FUNCTION IF EXISTS strip_tags||
CREATE FUNCTION strip_tags( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE sstart INT UNSIGNED;
DECLARE ends INT UNSIGNED;
IF x IS NOT NULL THEN
SET sstart = LOCATE('<', x, 1);
REPEAT
SET ends = LOCATE('>', x, sstart);
SET x = CONCAT(SUBSTRING( x, 1 ,sstart -1) ,SUBSTRING(x, ends +1 )) ;
SET sstart = LOCATE('<', x, 1);
UNTIL sstart < 1 END REPEAT;
END IF;
return x;
END;

||

delimiter ;

mysql> SELECT strip_tags('<a href="HelloWorld.html"><B>Hi, mate!</B></a>') as strip_tags;

+------------+
| strip_tags |
+------------+
| Hi, mate!  |
+------------+

Posted by Steven Gath on October 31, 2011

I was looking for an initcap or ucfirst function that would uppercase the first letter of each word in a string and couldn't find one so I modified one of the functions here so it worked for any number of words. Hope it can help someone else.

DELIMITER $$
DROP FUNCTION IF EXISTS `initcap`$$
CREATE FUNCTION `initcap`(x varchar(255)) RETURNS varchar(255) CHARSET utf8
DETERMINISTIC
begin
set @out_str='';
set @l_str='';
set @r_str='';

set @pos=LOCATE(' ',x);
SELECT x into @r_str;
while (@pos > 0) DO
SELECT SUBSTRING(@r_str,1,@pos-1) into @l_str;
SELECT SUBSTRING(@r_str,@pos+1) into @r_str;
SELECT concat(@out_str,upper(substring(@l_str,1,1)),lower(substring(@l_str,2)),' ') into @out_str;
set @pos=LOCATE(' ',@r_str);
END WHILE;
SELECT concat(@out_str,upper(substring(@r_str,1,1)),lower(substring(@r_str,2))) into @out_str;
return trim(@out_str);
end$$

DELIMITER ;

Posted by Paul Caskey on November 10, 2011

For DATE and DATETIME operations, MySQL demands the year be first, then month, then day. In the USA, a common date format is month-day-year . This example converts a date from MM-DD-YYYY format to MySQL's preferred YYYY-MM-DD. It also works on M-D-YY input, or other shortcut forms. Change the '-' separate to '.' or whatever you need. This takes 6-13-2011 and returns a STRING of '2011-6-13':

SELECT CONCAT(SUBSTRING_INDEX(DateMDY, '-', -1), '-', SUBSTRING_INDEX(DateMDY, '-', 2))

Now you can CAST this to a DATE, and then it will ORDER BY or GROUP BY properly. E.g. this takes '11.1.2011' and returns a real DATE of 2011-11-01. As usual I'm sure there are other ways to do this. I was just happy to figure this out without resorting to PHP or Perl.

CAST(CONCAT(SUBSTRING_INDEX(DateMDY, '.', -1), '.', SUBSTRING_INDEX(DateMDY, '.', 2)) AS DATE)

Posted by Manish Singh on November 17, 2011

If column of a table contains JSON string, then we can extract the value corresponding to a json key:

select substring_index(substring_index(json_params_column, 'jsonKey":"', -1), '"', 1) from MyTable;

Posted by Jesse Perring on December 16, 2011

To split a field out into one row for each separator in the field (i.e. if you have a field with a variable number of comma separated values in each row, but you want a table with a row for every value in that list):

The table I started with was something like...
id, list_of_types
(champion_1), (mage, fighter, support)
(champion_2), (support, mage)
(champion_3), (tank, support)

and the table I needed was....
id, type
champion_1, mage
champion_1, fighter
champion_1, support
champion_2, support
champion_3, tank
champion_3, support

So I ran...

select tOut.*, replace(substring(substring_index(type, ',', ocur_rank), length(substring_index(type, ',', ocur_rank - 1)) + 1), ',', '')
from (select @num_type := if(@id_check = tY.id, @num_type + 1, 1) as ocur_rank, @id_check := tY.id as id_check, tY.*
from (select LENGTH(list) - length(replace(list, ',', '')) as num_ocur, id, list from item) tX
inner join (select LENGTH(list) - length(replace(list, ',', '')) as num_ocur, id, list from item) tY
inner join (select @num_type := 0, @id_check := 'some_id') tZ) tOut
where ocur_rank <= num_ocur + 1;

Where "id" is just some unique identifier for each field you're splitting up and "list" is the list of separated values. The thought behind the query is to just join a table to itself, rank the rows for each id, then only show rows where the rank is less than the number of occurrences of the separator in the list you're splitting up. The outter most select then shows the value in between the rank and rank + 1 occurrence of the separator in the list.

This may not work if some of the lists don't have any occurrence of the separator.

Posted by Joseph Edmonds on January 19, 2012

Note concat can be used to compare two columns in the same query

so if you want to check if the contents of one column are in another column

http://www.edmondscommerce.co.uk/mysql/compare-two-columns-in-mysql/

Posted by halászsándor halászsándor on February 13, 2012

I had the same problem that Edmonds described, and for that I used this expression:

LOCATE(col1, col2) > 0 -- if "col1" is found in "col2"
OR
LOCATE(col2, col1) > 0 -- if "col2" is found in "col1"

Posted by Jez Gomez on April 18, 2012

I wanted to get the first portion of an email address, before the @ (ie, left of the @):

SELECT SUBSTRING_INDEX(email,'@',1) FROM <table> WHERE email REGEXP '@'

Posted by Mariano Otero on June 22, 2012

MySQL does not include a function to split a delimited string. However, it’s very easy to create your own function. I've added an example here: http://www.indumental.com/in/software/mysql.html. If you follow the example on that page, you'll be able to do something like this:

SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

-Mo

Posted by Alexandre Nunes on July 6, 2012

Suppose you have a text column with some delimiter separated data, and you want to get tokens from it, i.e. like strtok() would.

Ex, for the text "0,1,3,5,6", you wanna get the third element. This would do the trick:

select substring_index(substring_index(column,',',3),',',-1) as third;

P.S..: This seems a simplification of the previous example (which I managed to miss)

Posted by die manto on February 7, 2013

Tip to compare two columns:

SELECT *
FROM
`table`
WHERE
`col1` LIKE CONCAT('%', `col2`, '%')
OR col2 LIKE CONCAT('%',`col1`,'%')

Posted by http://www.competenciaperfecta.com/

Posted by Gunter Sammet on April 5, 2013

I was looking for a PHP equivalent for strpos and strrpos to get in combination with substring the string after the last occurrence of a character. Have a look at MYSQL SUBSTRING_INDEX. It may does what you want.
One of the first search results on another website suggested to use the locate function and if you need the last occurrence, use reverse string before using locate. Then use reverse again to get the correct sting. Just thought I'll post here with the important keywords, so it shows up in search results in case somebody else is looking for the same. HTH

Posted by Biniam Kefale on May 31, 2013

Example of using CONCAT() and UPDATE

This code updates the column named 'phone_number' in the table called 'user' by concatenating '0' in front of the new phone_number.

The new phone_number is old phone_number minus the first 4 characters or beginning from the 5th character.

The update will only be applied to the records with id between 3 and 30 exclusive.

UPDATE user SET phone_number = CONCAT('0', SUBSTRING(phone_number, 5)) WHERE id > 3 AND id < 30;

See how the code is shorter than the explanation?

Biniam from Ethiopia.

Posted by Adrian Humphreys on February 22, 2014

Here's a function to Propercase all the words in a string:

CREATE FUNCTION fn_propercase
(p_string Varchar(254)) RETURNS Varchar
BEGIN
SET @out="";
SET @x = 1;
SET @len = LENGTH(p_string);
/* Always make the 1st char uppercase. Set the flag on. */
SET @uc = 1;

REPEAT
SET @char= MID(p_string,@x,1);
IF @uc=1 THEN
SET @out= CONCAT(@out,UPPER(@char));
ELSE
SET @out= CONCAT(@out,LOWER(@char)) ;
END IF;

/* AFTER the char is written, test whether it is blank
if so, the NEXT char will be uppercase. */
IF @char=" " THEN
SET @uc= 1;
ELSE
SET @uc= 0;
END IF;

SET @x= @x + 1;
UNTIL @x > @len END REPEAT;

RETURN @out;

END

Posted by Shivakumar Durg on August 7, 2014

The following formula can be used to extract the Nth item in a delimited list.

SET @str:='mba,bca,mca,Bed'; # Your Complete String
SET @length:=LENGTH(@str);
SET @limit:=@length-LENGTH(REPLACE(@str,',',''))+1;
SET @pos:=2; # Substring position value
SET @tmp:=REVERSE(SUBSTRING_INDEX(@str,',',@pos));
SELECT IF(@limit>=@pos,
IF(@pos=1,SUBSTRING_INDEX(@str,',',@pos),REVERSE(SUBSTRING(@tmp,1,LOCATE(',',@tmp)-1))),'Not Exist')AS "Required_String"

Posted by EE Durham on August 10, 2014

Many thanks to Adrian Humphreys for his Propercase / Titlecase example: great idea :) Couldn't get it to work as-is, but here is a working version :)
Cheers!

DROP FUNCTION IF EXISTS str_titlecase;
CREATE FUNCTION str_titlecase (p_string varchar(254)) RETURNS varchar (254)
## by Adrian Humphreys, edited by Durham
## for given string, concatenate Capitalized first letter of
## each given word with Lower Case remainder of word
BEGIN
DECLARE output_string VARCHAR(254) DEFAULT '';
DECLARE temp_string VARCHAR(254);
DECLARE x INT DEFAULT 1; /*tracking variable*/
DECLARE uc INT DEFAULT 1; /*uppercase flag*/
DECLARE input_string_length INT DEFAULT LENGTH(p_string);

IF p_string IS NOT NULL THEN
REPEAT
SET temp_string := MID(p_string,x,1);
IF uc=1 THEN
SET output_string := CONCAT(output_string,UPPER(temp_string));
ELSE
SET output_string := CONCAT(output_string,LOWER(temp_string)) ;
END IF;

/* AFTER the char is written, test whether it is blank
if so, the NEXT char will be uppercase. */
IF temp_string=' ' THEN
SET uc := 1;
ELSE
SET uc := 0;
END IF;

SET x := x + 1;
UNTIL x > input_string_length END REPEAT;
END IF;

RETURN output_string;

END

#########################
## Usage (all lower case input):
select str_titlecase('i am a cat') as title from dual;

## Results:
title
------
I Am A Cat

## Usage (blank string case):
select str_titlecase('') as title from dual;

## Results:
title
------

## Usage (all upper case input):
select str_titlecase('I AM A DOLPHIN') as title from dual;

## Results:
title
------
I Am A Dolphin

## Usage (mixed-case input):
select str_titlecase('I am THE PRODUCT of your IMAGinatioN') as title from dual;

## Results:
title
------
I Am The Product Of Your Imagination

Posted by Luis Rocha on March 27, 2015

Found this http://stackoverflow.com/a/18218191 but it would not work correctly. So I wrote my own function based on that. You might want to filter the results a bit before calling this function (i.e. "SELECT * FROM abc WHERE haystack LIKE '%needle%' AND hasString(haystack, 'needle')..."). It runs rather slow otherwise.

CREATE FUNCTION `hasString`(haystack TINYTEXT, needle TINYTEXT) RETURNS TINYINT(1)
BEGIN
DECLARE needleFound TINYINT(1);
DECLARE inipos INTEGER;
DECLARE endpos INTEGER;
DECLARE maxlen INTEGER;
DECLARE item VARCHAR(100);
DECLARE delim VARCHAR(1);

SET delim = ',';
SET inipos = 1;
SET endpos = 0;
SET needleFound = 0;
SET maxlen = LENGTH(haystack);

REPEAT
SET endpos = LOCATE(delim, haystack, inipos);
SET item = SUBSTR(haystack, inipos, endpos - inipos);

IF inipos = 1 AND endpos = 0 THEN
IF haystack = needle THEN
SET needleFound = 1;
END IF;
SET endpos = maxlen + 100;
ELSE
IF inipos > 1 AND endpos = 0 THEN
SET endpos = maxlen + 50;
ELSE
IF item <> '' AND item IS NOT NULL THEN
IF item=needle THEN
SET needleFound = 1;
END IF;
ELSE
SET inipos = maxlen + 10;
END IF;
END IF;
END IF;
SET inipos = endpos + 1;
UNTIL inipos >= maxlen END REPEAT;

RETURN needleFound;
END

Posted by Jens Walte on April 29, 2015

fastest split() function

/**
* #1: this way is 10-20% faster than #3 and supports not included indexes otherwise than #2
*
* @example: split('a|bbb|cc', '|', 0) -> 'a'
* @example: split('a|bbb|cc', '|', 1) -> 'bbb'
* @example: split('a|bbb|cc', '|', 2) -> 'cc'
* @example: split('a|bbb|cc', '|', 3) -> ''
*/
substring_index(substring_index(concat(content, delimiter), delimiter, index+1), delimiter, -1);

/**
* #2: faster than #3, but not included index will return last entry
*
* @see: Posted by Mariano Otero on June 22 2012 3:43pm
* @example: split('a|bbb|cc', '|', 0) -> 'a'
* @example: split('a|bbb|cc', '|', 1) -> 'bbb'
* @example: split('a|bbb|cc', '|', 2) -> 'cc'
* @example: split('a|bbb|cc', '|', 3) -> 'cc' (unexpected)
*/
substring_index(substring_index(content, delimiter, index+1), delimiter, -1);

/**
* #3: first introduced split example, supports not included indexes
*
* @see: Posted by Bob Collins on March 17 2006 8:56pm
* @example: split('a|bbb|cc', '|', 0) -> 'a'
* @example: split('a|bbb|cc', '|', 1) -> 'bbb'
* @example: split('a|bbb|cc', '|', 2) -> 'cc'
* @example: split('a|bbb|cc', '|', 3) -> ''
*/
replace(substring(substring_index(content, delimiter, index+1), length(substring_index(content, delimiter, index)) + 1), delimiter, '');