MySQL string functions (recommended) and mysql Functions

MySQL string functions (recommended) and mysql Functions

I. ASCII

ASCII (str)

Returns the ASCII code value of the leftmost character of the str string. If str is a Null String, 0 is returned. If 'str' is NULL, return NULL.

2. ORD

ORD (str)

If the leftmost character of a string 'str' is a multi-byte character, use the format (first byte ASCII code) * 256 + (second byte ASCII code )) [* 256 + third byte ASCII code...] returns the ASCII code value of a character to return the multi-byte code. If the leftmost character is not a multi-byte character. Returns the same value as that returned by the ASCII () function.

Iii. CONV

CONV (N, from_base, to_base)
Convert numbers between different digit bases. Returns the string Number of number N, which is converted from from_base base to to_base base. If any parameter is NULL, NULL is returned. Parameter N is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is considered as a signed number. Otherwise, N is considered as an unsigned number. 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,-18);-> '-H'mysql> select CONV(10+"10"+'10'+0xa,10,10);-> '40'

Iv. BIN

BIN (n)

Returns a string of the binary value N. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 2 ). If N is NULL, return NULL.

V. OCT

OCT (N)
Returns the representation of a string with an octal value N. Here N is a long integer, which is equivalent to CONV (N, 10, 8 ). If N is NULL, return NULL.

Vi. HEX

HEX (N)
Returns the representation of a hexadecimal value N string. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 16 ). If N is NULL, return NULL.
Mysql & gt; select HEX (255 );

VII. CHAR

CHAR (N ,...)
CHAR () interprets the parameter as an integer and returns a string consisting of 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'

8. CONCAT/CONCAT_WS

• CONCAT (str1, str2 ,...)

Returns a string from the parameter link. If any parameter is NULL, return NULL. There can be more than two parameters. A numeric parameter is converted to an equivalent string.

mysql> select CONCAT('My', 'S', 'QL');-> 'MySQL'mysql> select CONCAT('My', NULL, 'QL');-> NULLmysql> select CONCAT(14.3);-> '14.3'

• CONCAT_WS (separator, str1, str2 ,...)

CONCAT_WS () represents CONCAT With Separator, which is a special form of CONCAT. The first parameter is the delimiter of other parameters. The separator is placed between the two strings to be connected. The delimiter can be a string or another parameter.

SELECT CONCAT_WS(";",id,title) FROM my_table LIMIT 100;SELECT CONCAT_WS(";",'aa','bb') FROM my_table

9. LENGTH/OCTET_LENGTH/CHAR_LENGTH/CHARACTER_LENGTH

LENGTH (str)/OCTET_LENGTH (str): number of bytes

CHAR_LENGTH (str)/CHARACTER_LENGTH (str): number of characters

10. LOCATE

This function is multi-byte reliable.

LOCATE (substr, str)
Returns the position of the substring substr In the first occurrence of the str. If the substring is not in the str, the return value is 0.

LOCATE (substr, str, pos)
Returns the position of the substring substr at the first occurrence of the substring, starting from the position pos. If substr is not in str, 0 is returned.

11. LPAD/RPAD

LPAD (str, len, padstr)
Returns the str string. The left side is filled with the string padstr until str is a string of len characters.
RPAD (str, len, padstr)
Returns the str string. Fill it with the string padstr on the right until it is a string of len characters.
 

12. LELT/RIGHT

LEFT (str, len)
Returns the leftmost len character of the str string.

RIGHT (str, len)
Returns the rightmost len character of the str string.
 

13. SUBSTRING

SUBSTRING (str, pos, len)

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

SUBSTRING (str, pos)

Returns a substring from the start position of the str string pos.

14. SUBSTRING_INDEX

SUBSTRING_INDEX (str, delim, count)

Returns the substring after the delimiter delim that appears from the count of the str string. If count is a positive number, all characters from the last separator to the left (from the left) are returned. If count is a negative number, return all characters (from the right) from the last separator to 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'

15. TRIM/LTRIM/RTRIM

TRIM ([BOTH | LEADING | TRAILING] [remstr] FROM] str)
Returns the str string. All the remstr prefixes or suffixes are deleted. If no modifier BOTH, LEADING, or TRAILING is given, BOTH is assumed. If remstr is not specified, spaces are 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 str string with leading space characters deleted.

RTRIM (str)
Returns the str string that deletes the trailing space characters.

16. SPACE

SPACE (N)
Returns a string consisting of N spaces.

17. REPLACE

REPLACE (str, from_str, to_str)
Returns the str string, which is replaced by the to_str character string.

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

18. REPEAT

REPEAT (str, count)
Returns a string consisting of str strings that repeat countTimes. If count <= 0, an empty string is returned. If 'str' or 'Count' is NULL, return NULL.

19. REVERSE

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

20. INSERT

INSERT (str, pos, len, newstr)
Returns the str string, which is the substring starting from the position pos and is replaced by the newstr string with the len character length.

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 parameters, NULL is returned. ELT () is a FIELD () inverse operation.

22. FIELD

FIELD (str, str1, str2, str3 ,...)
Returns the index of str in the str1, str2, str3,... list. If str is not found, 0 is returned. FIELD () is an inverse operation of ELT.

23. FIND_IN_SET

FIND_IN_SET (str, strlist)
If the string 'str' is in the strlist consisting of N substrings, a value ranging from 1 to N is returned. A string table is a string consisting of substrings separated by commas. If the first parameter is a constant string and the second parameter is a SET column, the FIND_IN_SET () function is optimized and bit operations are used! If str is not in strlist or if strlist is a Null String, 0 is returned. If any parameter is NULL, NULL is returned. If the first parameter contains a ",", the function will not work properly.

24. MAKE_SET

MAKE_SET (bits, str1, str2 ,...)
Returns a collection (a string consisting of substrings separated by ","), which is composed of the strings of the corresponding bits. Str1 corresponds to bit 0, str2 corresponds to bit 1, and so on. The NULL String in str1, str2,... 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. Here, for each bit set in "bits", you get a "on" string, and for each reset (reset) bit, you get a "off" string. Each string is separated by "separator" (default ","), and only the "number_of_bits" (default 64) bits are used.

26. LOWER/LCASE/UPPER/UCASE

LCASE (str)/LOWER (str): returns the str string, which is changed to lowercase based on the current character set ing (ISO-8859-1 Latin1 by default. This function is reliable for multiple bytes.

UCASE (str)/UPPER (str): returns the str string, which is changed to uppercase based on the current character set ing (ISO-8859-1 Latin1 by default. This function is reliable for multiple bytes.

27th, LOAD_FILE

LOAD_FILE (file_name)
Read the file and return the file content as a string. The file must be on the server, you must specify the full path name of the file, and you must have the file permission. All content of the file must be readable and smaller than max_allowed_packet. If the file does not exist or cannot be read due to one of the above reasons, the function returns NULL.

The above is all the MySQL string function details (recommended) provided by the editor. I hope you can provide more support for our customers ~