Name | Description | 例子 |
---|---|---|
ASCII(str) | 返回字符ascii码 | SELECT ASCII('2'); # -> 50 |
BIN(N) | 返回字符的二进制值 | SELECT BIN(12); # -> '1100' |
BIT_LENGTH() | Return length of argument in bits | |
CHAR(N,... [USING charset_name]) | 将整型转换为字符 | SELECT CHAR(77,121,83,81,'76'); # -> 'MySQL' |
CHAR_LENGTH() | Return number of characters in argument | |
CHARACTER_LENGTH() | Synonym for CHAR_LENGTH() | |
CONCAT(str1,str2,...) | 拼贴字符串 | SELECT CONCAT('My', 'S', 'QL'); # -> 'MySQL' |
CONCAT_WS() | Return concatenate with separator | |
ELT() | Return string at index number | |
EXPORT_SET() | Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string | |
FIELD(str,str1,str2,str3,...) | Returns the index (position) of str in the str1, str2, str3, … list. | SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff'); # -> 2 |
FIND_IN_SET() | Index (position) of first argument within second argument | |
FORMAT(X,D[,locale]) | 格式化字符串,D是小数点保留位 | SELECT FORMAT(12332.123456, 4); # -> '12,332.1235' |
FROM_BASE64() | Decode base64 encoded string and return result | |
HEX() | Hexadecimal representation of decimal or string value | |
INSERT(str,pos,len,newstr) | 插入字符 | SELECT INSERT('Quadratic', 3, 4, 'What'); # -> 'QuWhattic' |
INSTR(str,substr) | 索引子字符串 | SELECT INSTR('foobarbar', 'bar'); # -> 4 |
LCASE() | Synonym for LOWER() | |
LEFT(str,len) | 从左边截取字符串 | SELECT LEFT('foobarbar', 5); # -> 'fooba' |
LENGTH(str) | 返回字符串长度 | |
LIKE | Simple pattern matching | |
LOAD_FILE() | Load the named file | |
LOCATE(substr,str), LOCATE(substr,str,pos) | 索引字符串 | SELECT LOCATE('bar', 'foobarbar'); # -> 4 |
LOWER(str) | 小写 | SELECT LOWER('QUADRATICALLY'); -> 'quadratically' |
LPAD(str,len,padstr) | 左边填充字符串,len是填充后的长度 | SELECT LPAD('hi',4,'??'); # -> '??hi' |
LTRIM(str) | 删除左边的空白 | SELECT LTRIM(' barbar'); # -> 'barbar' |
MAKE_SET() | Return a set of comma-separated strings that have the corresponding bit in bits set | |
MATCH | Perform full-text search | |
MID(str,pos,len) | SUBSTRING(str,pos,len) | |
NOT LIKE | Negation of simple pattern matching | |
NOT REGEXP | Negation of REGEXP | |
OCT() | Return a string containing octal representation of a number | |
OCTET_LENGTH() | Synonym for LENGTH() | |
ORD() | Return character code for leftmost character of the argument | |
POSITION() | Synonym for LOCATE() | |
QUOTE() | Escape the argument for use in an SQL statement | |
REGEXP | Whether string matches regular expression | |
REGEXP_INSTR() | Starting index of substring matching regular expression | |
REGEXP_LIKE() | Whether string matches regular expression | |
REGEXP_REPLACE() | Replace substrings matching regular expression | |
REGEXP_SUBSTR() | Return substring matching regular expression | |
REPEAT(str,count) | 重复字符串 | SELECT REPEAT('MySQL', 3); # -> 'MySQLMySQLMySQL' |
REPLACE(str,from_str,to_str) | 替换字符串 | SELECT REPLACE('www.mysql.com', 'w', 'Ww'); # -> 'WwWwWw.mysql.com' |
REVERSE(str) | 反转字符串 | SELECT REVERSE('abc'); # -> 'cba' |
RIGHT(str,len) | 从右边截取字符串 | SELECT RIGHT('foobarbar', 4); # -> 'rbar' |
RLIKE | Whether string matches regular expression | |
RPAD(str,len,padstr) | 右边填充空白 | SELECT RPAD('hi',5,'?'); # -> 'hi???' |
RTRIM(str) | 右边删除空白 | SELECT RTRIM('barbar '); # -> 'barbar' |
SOUNDEX() | Return a soundex string | |
SOUNDS LIKE | Compare sounds | |
SPACE(N) | 生成空白字符 | SELECT SPACE(6); # ->-> ' ' |
STRCMP() | Compare two strings | |
SUBSTR() | Return the substring as specified | |
SUBSTRING(str,pos,len) | 截取字符串 | SELECT SUBSTRING('Quadratically',5,6); # -> 'ratica' |
SUBSTRING_INDEX() | Return a substring from a string before the specified number of occurrences of the delimiter | |
TO_BASE64() | Return the argument converted to a base-64 string | |
TRIM([{BOTH .... | 删除空白 | SELECT TRIM(' bar '); # -> 'bar' |
UCASE() | UPPER() | |
UNHEX() | Return a string containing hex representation of a number | |
UPPER(str) | 大写 | SELECT UPPER('Hej'); # -> 'HEJ' |
WEIGHT_STRING() | Return the weight string for a string |
参考:
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html