MySQL string function

First, ASCII

ASCII (str)

Returns the ASCII code value of the leftmost character of the string str. If Str is an empty string, return 0. If STR is NULL, returns NULL.

Second, ORD

ORD (str)

If the string str the leftmost character is a multibyte character, by using the format (first byte ASCII code) *256+ (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 multi-byte character. Returns the same value returned from the ASCII () function.

Third, CONV

CONV (N,from_base,to_base)
The numbers are transformed between different digital bases. Returns a string number of N, from the From_base base transform 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 as 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 count, otherwise n is treated as an unsigned number. The conv works with 64-bit precision.

Mysql> Select CONV ("a", 16,2);-> ' 1010 ' mysql> select CONV ("6E", 18,8);-> ' 172 ' mysql> select CONV ( -17,10,- ;-> '-h ' mysql> Select CONV (+ + ' + ' +0xa,10,10);-> ' 40 '

Four, BIN

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.

V. OCT

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.

VI. Hex

HEX (N)
Returns the hexadecimal value n a string representation, 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);

Seven, CHAR

CHAR (N,...)
CHAR () interprets parameters as integers and returns a string consisting of ASCII code characters for those integers. A null value was skipped.

Mysql> Select char (77,121,83,81, ' $ ');-> ' MySQL ' mysql> select char (77,77.3, ' 77.3 ');-> ' MMM '

Eight, CONCAT

CONCAT (STR1,STR2,...)
Returns the string from the parameter link. Returns NULL if any parameter is null. There can be 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 ');-> nullmysql> Selec T CONCAT (14.3);-> ' 14.3 '

Nine, Length/octet_length/char_length/character_length

LENGTH (str)/octet_length (str): Number of bytes

Char_length (str)/character_length (str): Number of characters

Ten, LOCATE

The function is multi-byte reliable.

LOCATE (SUBSTR,STR)
Returns the substring substr in the first occurrence of the string str, if SUBSTR is not inside STR, returns 0.

LOCATE (Substr,str,pos)
Returns the substring substr where the first occurrence of the string Str begins, starting at position pos. If the substr is not inside STR, return 0.

Xi. Lpad/rpad

Lpad (STR,LEN,PADSTR)
Returns the string str, left with the string padstr filled until STR is Len characters long.
Rpad (STR,LEN,PADSTR)
Returns the string str, to the right with a string padstr until Str is len characters long.

12, Lelt/right

Left (Str,len)
Returns the leftmost Len character of a string str.

Right (Str,len)
Returns the rightmost Len character of a string str.

13, SUBSTRING

SUBSTRING (Str,pos,len)

Returns a substring of Len characters from the string str, starting at position pos.

SUBSTRING (Str,pos)
Returns a substring from the starting position of the string str from POS.

14, Substring_index

Substring_index (Str,delim,count)
Returns the substring that appears after the delimiter Delim from the count of the string str. If Count is a positive number, returns all characters from the last delimiter to the left (from the left). If count is negative, returns the last delimiter to the right of all characters (from the right number).
This function is reliable for multiple bytes.

Mysql> Select Substring_index (' www.mysql.com ', '. ', 2);-> ' Www.mysql ' mysql> select Substring_index (' Www.mysql.com ', '. ',-2);-> ' mysql.com '

XV, Trim/ltrim/rtrim

TRIM ([BOTH | Leading | TRAILING] [REMSTR] from] str)
Returns the string str with all of its remstr prefixes or suffixes removed. If no modifier both, leading, or trailing is given, both is assumed. If REMSTR is not specified, the space is deleted.

Mysql> SelectTRIM ('Bar'); - 'Bar'MySQL> SelectTRIM (Leading'x'  from 'xxxbarxxx'); - 'barxxx'MySQL> SelectTRIM (BOTH'x'  from 'xxxbarxxx'); - 'Bar'MySQL> SelectTRIM (TRAILING'XYZ'  from 'barxxyz'); - 'Barx'

LTRIM (str)
Returns the string str whose preceding space character was deleted.

RTRIM (str)
Returns the string str whose trailing space character was deleted.

16. SPACE

SPACE (N)
Returns a string consisting of n space characters.

17. REPLACE

REPLACE (STR,FROM_STR,TO_STR)
Returns the string str, whose string from_str all occurrences are replaced by the string to_str.

Mysql> Select REPLACE (' www.mysql.com ', ' w ', ' Ww ');-> ' WwWwWw.mysql.com '

　　

18, REPEAT

REPEAT (Str,count)
Returns a string consisting of the string str that repeats counttimes times. If Count <= 0, an empty string is returned. If STR or count is NULL, returns NULL.

19, REVERSE

REVERSE (str)
Returns a string that reverses the character order of Str.

20. INSERT

INSERT (STR,POS,LEN,NEWSTR)
Returns the string str, a substring starting at position pos, and Len characters long substrings are replaced by string newstr.

Mysql> Select INSERT (' Quadratic ', 3, 4, ' What ');-> ' quwhattic '

　　

21. ELT

ELT (N,STR1,STR2,STR3,...)
If n= 1, return str1, if n= 2, return str2, and so on. If n is less than 1 or greater than the number of arguments, NULL is returned. ELT () is a field () inverse operation.

22. FIELD

FIELD (STR,STR1,STR2,STR3,...)
Return str in str1, str2, STR3, ... The index of the manifest. If STR is not found, return 0. FIELD () is an ELT () inverse operation.

23, Find_in_set

Find_in_set (Str,strlist)
If the string str is in table strlist consisting of n substrings, returns a value of 1 to N. 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 to use bit arithmetic! If STR is not inside strlist or if Strlist is an empty string, return 0. Returns null if any one of the arguments is null. If the first parameter contains a ",", the function will not work correctly.

24, Make_set

Make_set (BITS,STR1,STR2,...)
Returns a collection (a string consisting of substrings separated by "," characters), consisting of a string of corresponding bits in the bits collection. The str1 corresponds to bit 0,str2 corresponding to 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 ');-> "

　　

25, Export_set

Export_set (Bits,on,off,[separator,[number_of_bits])

Returns a string where you get an "on" string for each bit in "bits", and for each reset (reset) bit, you get a "off" string. Each string is delimited with "separator" (Default ","), and only "bits" of "number_of_bits" (default 64) bits are used.

26, Lower/lcase/upper/ucase

LCASE (str)/lower (str): Returns the string str, changing all characters to lowercase according to the current character set mapping (the default is Iso-8859-1 Latin1). This function is reliable for multiple bytes.

UCASE (str)/upper (str): Returns the string str, changing all characters to uppercase based on the current character set mapping (the default is Iso-8859-1 Latin1). This function is reliable for multiple bytes.

27, Load_file

Load_file (file_name)
Reads the file and returns the file contents as a string. The file must be on the server, you must specify the full path name to the file, and you must have file permissions. The file must have all the content readable and less than max_allowed_packet. If the file does not exist or because one of the above reasons cannot be read, the function returns NULL.

Reference:

• MYSQL string function

　　

　

MySQL string function