Summary: MySQL database commonly-character processing functions

ASCII (str)
Returns the ASCII code value of the leftmost character of the string str. If Str is an empty string, returns 0. If STR is NULL, returns NULL.
Mysql> Select ASCII (' 2 ');
-> 50
Mysql> Select ASCII (2);
-> 50
Mysql> Select ASCII (' DX ');
-> 100
You can also see the Ord () function.

ORD (str)
If the string str leftmost character is a multibyte character, by using the format (the *256+ (the second byte ASCII code)) [*256+third byte ASCII code ...] Returns the ASCII code value of the character to return the multibyte character code. If the leftmost character is not a multibyte character. Returns the same value returned with the ASCII () function.
Mysql> Select ORD (' 2 ');
-> 50

CONV (N,from_base,to_base)
Transforms a number between different numeric bases. Returns a string number for the number n, from the From_base base transformation to the to_base base, or null if any argument is null. Parameter n is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the largest base is 36. If To_base is a negative number, n is considered to be a signed digit, otherwise n is treated as unsigned. Conv work with 64-bit precision.
Mysql> Select CONV ("a", 16,2);
-> ' 1010 '
Mysql> Select CONV ("6E", 18,8);
-> ' 172 '
Mysql> Select CONV ( -17,10,-18);
-> '-h '
Mysql> Select CONV (10+ "ten" + ' +0xa,10,10 ');
-> ' 40 '

BIN (N)
Returns a string representation of the binary value N, where n is a long integer (BIGINT) number, which is equivalent to Conv (n,10,2). If n is null, returns NULL.
Mysql> Select BIN (12);
-> ' 1100 '

OCT (N)
Returns the representation of a string of octal value N, where n is a long integer number, which is equivalent to Conv (n,10,8). If n is null, returns NULL.
Mysql> Select OCT (12);
-> ' 14 '

HEX (N)
Returns the hexadecimal value n the representation of a string, where n is a long integer (BIGINT) number, which is equivalent to Conv (n,10,16). If n is null, returns NULL.
Mysql> Select HEX (255);
-> ' FF '

CHAR (N,...)
CHAR () interprets the argument as an integer and returns a string consisting of the ASCII code characters of these integers. The null value is skipped.
Mysql> Select CHAR (77,121,83,81, ' 76 ');
-> ' MySQL '
Mysql> Select CHAR (77,77.3, ' 77.3 ');
-> ' MMM '

CONCAT (STR1,STR2,...)
Returns a string from the argument's link. Returns null if any argument is null. can have more than 2 parameters. A numeric parameter is transformed into an equivalent string form.
Mysql> Select CONCAT (' My ', ' S ', ' QL ');
-> ' MySQL '
Mysql> Select CONCAT (' My ', NULL, ' QL ');
-> NULL
Mysql> Select CONCAT (14.3);
-> ' 14.3 '

LENGTH (str)
　
Octet_length (str)
　
Char_length (str)
　
Character_length (str)
Returns the length of the string str.
Mysql> Select LENGTH (' text ');
-> 4
Mysql> Select Octet_length (' text ');
-> 4

Note that for multibyte characters, its char_length () is evaluated only once.

LOCATE (SUBSTR,STR)
　
POSITION (substr in str)
Returns the substring substr the first occurrence of the string str, if SUBSTR is not inside STR, returns 0.
Mysql> Select LOCATE (' Bar ', ' Foobarbar ');
-> 4
Mysql> Select LOCATE (' Xbar ', ' foobar ');
-> 0

The function is multiple-byte reliable.
LOCATE (Substr,str,pos)
Returns the substring substr the position of the first occurrence of the string str, starting at the location POS. If SUBSTR is not inside STR, return 0.
Mysql> Select LOCATE (' Bar ', ' Foobarbar ', 5);
-> 7

This function is multibyte-reliable.

INSTR (STR,SUBSTR)
Returns the position of the substring substr the first occurrence in string str. This is the same as the locate () with 2 parameter forms, except that the parameters are reversed.
Mysql> Select INSTR (' Foobarbar ', ' Bar ');
-> 4
Mysql> Select INSTR (' Xbar ', ' foobar ');
-> 0

This function is multibyte-reliable.

Lpad (STR,LEN,PADSTR)
Returns the string str, and the left is filled with a string padstr until Str is len characters long.
Mysql> Select Lpad (' Hi ', 4, '?? ');
-> '?? Hi

Rpad (STR,LEN,PADSTR)
Returns the string str, and the right is filled with a string padstr until Str is len characters long.
Mysql> Select Rpad (' Hi ', 5, '? ');
-> ' Hi??? '

Left (Str,len)
Returns the leftmost Len character of the string str.
Mysql> Select Left (' Foobarbar ', 5);
-> ' Fooba '

The function is multiple-byte reliable.

Right (Str,len)
Returns the rightmost Len character of the string str.
Mysql> Select Right (' Foobarbar ', 4);
-> ' Rbar '

The function is multiple-byte reliable.

SUBSTRING (Str,pos,len)
　
SUBSTRING (str from POS for Len)
　
MID (Str,pos,len)
Returns a substring of Len characters from String str, starting at position pos. The variant form using from is the ANSI SQL92 syntax.
Mysql> Select SUBSTRING (' quadratically ', 5,6);
-> ' Ratica '

The function is multiple-byte reliable.

SUBSTRING (Str,pos)
　
SUBSTRING (str from POS)
Returns a substring from the starting position of string Str.
Mysql> Select SUBSTRING (' quadratically ', 5);
-> ' ratically '
Mysql> Select SUBSTRING (' Foobarbar ' from 4);
-> ' Barbar '

The function is multiple-byte reliable.

Substring_index (Str,delim,count)
Returns a substring of Delim after the occurrence of the delimiter from the count of string str. If Count is a positive number, returns all characters from the last separator to the left (from the left). If count is a negative number, returns the last separator to all characters on the right (from the right).
Mysql> Select Substring_index (' www.webjx.com ', '. ', 2);
-> ' WWW.WEBJX '
Mysql> Select Substring_index (' www.webjx.com ', '. ',-2);
-> ' webjx.com '

This function is reliable for multiple bytes.

LTRIM (str)
Returns the string str whose preceding space character was deleted.
Mysql> Select LTRIM (' Barbar ');
-> ' Barbar '

RTRIM (str)
Returns the string str whose trailing space character has been deleted.
Mysql> Select RTRIM (' Barbar ');
-> ' Barbar '

This function is reliable for multiple bytes.
TRIM ([[BOTH | Leading | Trailing] [REMSTR] from] str)
Returns the string str with all of its remstr prefixes or suffixes removed. If no modifiers both, leading, or trailing are given, the both is assumed. If the REMSTR is not specified, the space is deleted.
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 '

This function is reliable for multiple bytes.

SOUNDEX (str)
Returns a homonym string for str. The 2 strings that sound "roughly the same" should have the same homonym string. A "standard" homonym string length is 4 characters, but the SOUNDEX () function returns an arbitrarily long string. You can use SUBSTRING () in the results to get a "standard" of the homophonic string. All non-numeric alphabetic characters are ignored in the given string. All characters outside A-Z are treated as vowels.
Mysql> Select SOUNDEX (' Hello ');
-> ' H400 '
Mysql> Select SOUNDEX (' quadratically ');
-> ' Q36324 '

Space (N)
Returns a string consisting of n white space characters.
Mysql> Select space (6);
-> '

REPLACE (STR,FROM_STR,TO_STR)
Returns the string str, where all occurrences of the string from_str are replaced by string to_str.
Mysql> Select REPLACE (' www.webjx.com ', ' w ', ' Ww ');
-> ' WwWwWw.webjx.com '

This function is reliable for multiple bytes.

REPEAT (Str,count)
Returns a string of string str that is repeated counttimes times. If Count <= 0, returns an empty string. Returns null if STR or count is null.
Mysql> Select REPEAT (' MySQL ', 3);
-> ' Mysqlmysqlmysql '

REVERSE (str)
Returns the string str of the reversed character order.
Mysql> Select REVERSE (' abc ');
-> ' CBA '

This function is reliable for multiple bytes.

INSERT (STR,POS,LEN,NEWSTR)
Returns the string str, the substring at the beginning of the position Pos and the Len character long substring is replaced by the string newstr.
Mysql> Select INSERT (' Quadratic ', 3, 4, ' What ');
-> ' quwhattic '

This function is reliable for multiple bytes.

ELT (N,STR1,STR2,STR3,...)
If n= 1, return str1, if n= 2, return str2, and so on. Returns NULL if n is less than 1 or greater than the number of arguments. ELT () is a field () inverse operation.
Mysql> Select ELT (1, ' ej ', ' Heja ', ' Hej ', ' foo ');
-> ' EJ '
Mysql> Select ELT (4, ' ej ', ' Heja ', ' Hej ', ' foo ');
-> ' foo '

FIELD (STR,STR1,STR2,STR3,...)
Back to Str in str1, str2, STR3, ... The index of the manifest. If STR is not found, return 0. FIELD () is an ELT () inverse operation.
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)
If string str returns a value of 1 to N in the table strlist composed of n substrings. A string table is a string consisting of "," separated substrings. If the first argument is a constant string and the second argument is a column of type set, the Find_in_set () function is optimized and the bitwise operation is used! If STR is not inside the strlist or if Strlist is an empty string, return 0. Returns null if any one of the arguments is null. If the first argument contains a "," the function will not work correctly.
mysql> SELECT find_in_set (' B ', ' a,b,c,d ');
       -> 2
 
Make_set (bits,str1,str2,...) The
returns a collection containing a string of substrings separated by the "," character, consisting of a string of corresponding bits in the bits collection. STR1 corresponds to bit 0,str2 corresponding bit 1, and so on. In str1, str2, ... The null string in is not added to the result.
mysql> SELECT make_set (1, ' A ', ' B ', ' C ');
       -> ' a '
mysql> SELECT make_set (1 | 4, ' hello ', ' nice ', ' world ');
       -> ' Hello,world '
mysql> SELECT make_set (0, ' a ', ' B ', ' C ');
       -> '

Export_set (Bits,on,off,[separator,[number_of_bits]])
Returns a string, where you get an "on" string for each bit in "bits", and you get a "off" string for each reset (reset) bit. Each string is delimited by "separator" (Default ",") and Only "Number_of_bits" (default 64) bits of "bits" are used.
Mysql> Select Export_set (5, ' Y ', ' N ', ', ', ', ', ', ', 4)
-> Y,n,y,n

LCASE (str)
　
LOWER (str)
Returns the string str, which changes all characters to lowercase according to the current character set mapping (default is Iso-8859-1 Latin1). This function is reliable for multiple bytes.
Mysql> Select LCASE (' quadratically ');
-> ' quadratically '

UCASE (str)
　
UPPER (str)
Returns the string str, which changes all characters to uppercase according to the current character set mapping (default is Iso-8859-1 Latin1). This function is reliable for multiple bytes.
Mysql> Select UCASE (' Hej ');
-> ' HEJ '

This function is reliable for multiple bytes.

Load_file (file_name)
Reads the file and returns the contents of the file as a string. Files must be on the server, you must specify the full pathname to the file, and you must have file permissions. The file must all content be readable and less than max_allowed_packet. The function returns null if the file does not exist or because one of the above reasons cannot be read out.
Mysql> UPDATE table_name
SET blob_column=load_file ("/tmp/picture")
WHERE id=1;

MySQL automatically transforms numbers into strings when necessary, and vice versa:

Mysql> Select 1+ "1";
-> 2
Mysql> SELECT CONCAT (2, ' test ');
-> ' 2 test '