Mysql string function detailed (recommended) _mysql

First, ASCII

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.

Second, ORD

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.

Third, CONV

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 '

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.

Five, 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 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);

Seven, CHAR

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, ' ");

-> ' MySQL '

mysql> Select CHAR (77,77.3, ' 77.3 ');

-> ' MMM '

Eight, Concat/concat_ws

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 '

Concat_ws (separator,str1,str2,...)

Concat_ws () represents CONCAT with Separator and is a special form of CONCAT (). The first parameter is the separator for the other arguments. The position of the separator is placed between the two strings to which you want to connect. The delimiter can be a string, or it can be another parameter.

SELECT Concat_ws (";", Id,title) from my_table LIMIT;

SELECT Concat_ws (";", ' AA ', ' BB ') from my_table

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 multiple-byte reliable.

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

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.

Xi. Lpad/rpad

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

12, Lelt/right

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

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

13, SUBSTRING

SUBSTRING (Str,pos,len)

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

SUBSTRING (Str,pos)

Returns a substring from the starting position of string Str.

14, Substring_index

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).
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 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 '

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

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

16, Space

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

17, REPLACE

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.mysql.com ', ' w ', ' Ww ');

-> ' WwWwWw.mysql.com '

18, REPEAT

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.

19, REVERSE

REVERSE (str)
Returns the string str of the reversed character order.

20, INSERT

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 '

21. ELT

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.

22. FIELD

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.

23, Find_in_set

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.

24, Make_set

Make_set (BITS,STR1,STR2,...)
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 ');

-> '

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 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.

26, Lower/lcase/upper/ucase

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.

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.

27, Load_file

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.

The above is a small series for everyone to bring the MySQL string function in detail (recommended) All content, I hope that we support cloud Habitat Community ~