Mysql string functions

Mysql string function usage MySQL marks the first position as 1 for operations on string positions. 1. 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. MysqlselectASCII (2 );

Mysql string function usage MySQL marks the first position as 1 for operations on string positions. 1. 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. Mysql select ASCII ('2');-50 mysql select ASCII (2);-50 mysql

Mysql string function usage MySQL

For operations on the string position, the first position is marked as 1. 1. 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. Mysql> select ASCII ('2');-> 50 mysql> select ASCII (2);-> 50 mysql> select ASCII ('dx '); -> 100 for more information, see the ORD () function. 2. 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. Mysql> select ORD ('2');-> 50 3. CONV (N, from_base, to_base) converts 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);-> '000000' mysql> select CONV ("6E", 1010 ); -> '200' mysql> select CONV (-172,-18);-> '-H' mysql> select CONV (10 + "10" + '10' + 0xa, 10, 10);-> '40' 4.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, return NULL. Mysql> select BIN (12);-> '20180101' 5. OCT (N) returns the representation of a string with an octal value of N, where N is a long integer, which is equivalent to CONV (N, 10, 8 ). If N is NULL, return NULL. Mysql> select OCT (12);-> '14' 6.HEX( N) returns the hexadecimal value N, a string representation, where N is a long integer (BIGINT) number, this is equivalent to CONV (N, 10, 16 ). If N is NULL, return NULL. Mysql> select HEX (255);-> 'ff '7. 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, 77.3, '76 ');-> 'mysql' MySQL> select CHAR (77, '77. 3 ');-> 'mmm' 8. 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'9. LENGTH (str) 10. OCTET_LENGTH (str) 11. CHAR_LENGTH (str) 12. CHARACTER_LENGTH (str) returns the length of the str string. Mysql> select LENGTH ('text');-> 4 mysql> select OCTET_LENGTH ('text');-> 4 Note that for multi-byte characters, its CHAR_LENGTH () only once. 13. LOCATE (substr, str) 14. POSITION (substr IN str) returns the POSITION of the substring substr IN the first occurrence of the substring. If the substring is not IN str, 0 is returned. mysql> select LOCATE ('bar', 'foobar');-> 4 mysql> select LOCATE ('xbar', 'foobar '); -> 0 this function is multi-byte reliable. 15. LOCATE (substr, str, pos) returns the position of the substring substr in the first position of the str string, starting from the position pos. If substr is not in str, 0 is returned. Mysql> select LOCATE ('bar', 'foobarbar', 5);-> 7 this function is multi-byte reliable. 16. INSTR (str, substr) returns the first position of the substring substr In the str string. This is the same as LOCATE () in the form of two parameters, except that the parameters are reversed. Mysql> select INSTR ('foobar', 'bar');-> 4 mysql> select INSTR ('xbar', 'foobar '); -> 0. This function is multi-byte reliable. 17. LPAD (str, len, padstr) returns the str string, which is filled in with the string padstr on the left until str is long as len. Mysql> select LPAD ('hi', 4 ,'?? ');-> '?? Hi '18. RPAD (str, len, padstr) returns the str string. Fill it with the string padstr on the right until str is long as len. Mysql> select RPAD ('hi', 5 ,'? ');-> 'Hi ??? '19. LEFT (str, len) returns the leftmost len character of the str string. Mysql> select LEFT ('foobarbar', 5);-> 'fooba' this function is multi-byte reliable. 20. RIGHT (str, len) returns the rightmost len character of the str string. Mysql> select RIGHT ('foobarbar', 4);-> 'rbar 'this function is multi-byte reliable. 21. SUBSTRING (str, pos, len) 22. SUBSTRING (str FROM pos FOR len) 23.MID( str, pos, len) returns a SUBSTRING of len characters FROM str, starting FROM position pos. The variant form of FROM is ANSI SQL92 syntax. Mysql> select SUBSTRING ('quadratically ', 5, 6);-> 'ratica' this function is reliable in multiple bytes. 24. SUBSTRING (str, pos) 25. SUBSTRING (str FROM pos) returns a substring from the start position of str. Mysql> select SUBSTRING ('quadratically ', 5);-> 'ratically' mysql> select SUBSTRING ('foobarbar' FROM 4 ); -> the 'barbar' function is multi-byte reliable. 26. SUBSTRING_INDEX (str, delim, count) returns the substring after the delimiter delim 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 ). Mysql> select SUBSTRING_INDEX ('www .mysql.com ','. ', 2);->' www. mysql 'mysql> select SUBSTRING_INDEX ('www .mysql.com ','. ',-2);->' mysql. com 'this function is reliable for multiple bytes. 27. LTRIM (str) returns the str string that deletes the leading space character. Mysql> select LTRIM ('barbar');-> 'barbar' 28. RTRIM (str) returns the string str with the trailing space characters deleted. Mysql> select RTRIM ('barbar');-> 'barbar' this function is reliable for multiple bytes. 29. TRIM ([[BOTH | LEADING | TRAILING] [remstr] FROM] str) returns the str string, and all its 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 '); -> 'barxx' mysql> select TRIM (BOTH 'X' FROM 'xxxbarxxx');-> 'bar' mysql> select TRIM (TRAILING 'xyz' FROM 'barxxyz '); -> 'barx': this function is reliable for multiple bytes. 21. SOUNDEX (str) returns a homophone string of str. The two strings that sound "roughly the same" should have the same homophone string. The length of a "standard" homophone string is 4 characters, but the SOUNDEX () function returns a string of any length. You can use SUBSTRING () in the result to obtain a "standard" homophone string. All non-alphanumeric characters are ignored in a given string. All international letters except the A-Z are treated as vowels. Mysql> select SOUNDEX ('hello');-> 'h400' mysql> select SOUNDEX ('quadratically ');-> 'q36324' 22. SPACE (N) returns a string consisting of n space characters. Mysql> select SPACE (6);-> ''23. REPLACE (str, from_str, to_str) returns the str string. All the from_str strings are replaced by the to_str string. Mysql> select REPLACE ('www .mysql.com ', 'w', 'ww');-> 'wwwwww .mysql.com 'this function is reliable for multiple bytes. 24. REPEAT (str, count) returns a string consisting of repeated countTimes. If count <= 0, an empty string is returned. If 'str' or 'Count' is NULL, return NULL. Mysql> select REPEAT ('mysqlmysqlmysql ', 3);-> 'mysqlmysqlmysql' 25. REVERSE (str) returns the string str in REVERSE character order. Mysql> select REVERSE ('abc');-> 'ba' this function is reliable for multiple bytes. 26. INSERT (str, pos, len, newstr) returns the str string, which is the substring starting from the position pos and replaces the string with newstr. Mysql> select INSERT ('quadratic ', 3, 4, 'whe');-> 'quwhattic' this function is reliable for multiple bytes. 27. ELT (N, str1, str2, str3,...) returns str1 if N = 1, returns str1 if N = 2, returns 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. Mysql> select ELT (1, 'ej', 'heja ', 'hej', 'foo');-> 'ej' mysql> select ELT (4, 'ej ', 'heja ', 'hej', 'foo');-> 'foo' 28. FIELD (str, str1, str2, str3 ,...) returns str in str1, str2, str3 ,... index of the list. If str is not found, 0 is returned. FIELD () is an inverse operation of ELT. Mysql> select FIELD ('ej', 'hej', 'ej', 'heja ', 'hej', 'foo '); -> 2 mysql> select FIELD ('fo', 'hej', 'ej', 'heja ', 'hej', 'foo');-> 029. FIND_IN_SET (str, strlist) if the string str is in the strlist of a table 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. Mysql> SELECT FIND_IN_SET ('B', 'a, B, c, D');-> 2 30. 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 '); -> ''31. EXPORT_SET (bits, on, off, [separator, [number_of_bits]) returns a string, where each bit is set in "bits, you get a "on" string and you get a "off" string for each reset bit. Each string is separated by "separator" (default ","), and only the "number_of_bits" (default 64) bits are used. Mysql> select EXPORT_SET (5, 'y', 'n', ',', 4)-> Y, N, Y, N 32. LCASE (str) 33. LOWER (str) returns the string str, which converts all characters to lowercase based on the current character set ing (ISO-8859-1 Latin1 by default. This function is reliable for multiple bytes. Mysql> select LCASE ('quadratically ');-> 'quadratically' 34. UCASE (str) 35. UPPER (str) returns the string str, 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. Mysql> select UCASE ('hej');-> 'hej' this function is reliable for multiple bytes. 36. LOAD_FILE (file_name) reads the file and returns 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. Mysql> UPDATE table_name SET blob_column = LOAD_FILE ("/tmp/picture") WHERE id = 1; MySQL automatically converts numbers to strings if necessary, and this is also true: mysql> SELECT 1 + "1";-> 2 mysql> select concat (2, 'test '); -> '2 test' If You Want To explicitly convert a number to a string, pass it as a parameter to CONCAT (). If a string function provides a binary string as a parameter, the result string is also a binary string. The number converted to a string is treated as a binary string. This only affects the comparison.