Brief introduction of commonly used character functions in MySQL

A brief introduction to MySQL commonly used character functions CONCAT (S1MagneS2... Sn) connects S1MagneS2. SN is a string concat function that concatenates the passed parameters into a string. For example: concatenate "aaa", "bbb" and "ccc" into a string, "aaabbbccc". In addition, the result of any connection to NULL will be NULL. > SELECT concat ('aaa','bbb','ccc'), concat (' aaa',NULL); mysql > SELECT concat ('aaa','bbb','ccc'), concat (' aaa',NULL) +-+-+ | concat ('aaa','bbb','ccc') | concat (' aaa') NULL) | +-+-+ | aaabbbccc | NULL | +-- +-+ 1 row in set (0.00 sec)

INSERT (str,x,y,instr) replaces the string str from the x position, the y character length substring with the string insert (str,x,y,instr) function: the string str starts from the x position, and the y character length substring is replaced with "me". SELECT insert ('beijingaiNI',10,2,'WO') +-- + | insert ('beijingaiNI',10,2 'WO') | +-+ | beijingaiWO | +-+ 1 row in set (0.00 sec)

LOWER (str); UPPER (str) changes all characters in the string str to lowercase or uppercase LOWER (str) and UPPER (str) functions: converts strings to lowercase or uppercase in string comparison, usually converting all the compared strings to uppercase or lowercase, as shown in the following example: mysql > SELECT lower ('WOAINI'), upper (' woxihuanni') +-+ | lower ('WOAINI') | upper (' woxihuanni') | +-+-+ | woaini | WOXIHUANNI | +- -+-+ 1 row in set (0.00 sec)

LEFT (str,x); RIGHT (str,x) returns the leftmost or rightmost x strings of the string LEFT (str,x) and RIGHT (str,x) functions: return the leftmost and rightmost x characters of the string, respectively, and if the second argument is NULL, no strings will be returned. The following example shows: mysql > SELECT left ('beijing',3); +-+ | left (' beijing',3) | +-+ | bei | +-+ 1 row in set (0.01 sec) mysql > SELECT left ('beijing',NULL) +-- + | left ('beijing',NULL) | +-+ | NULL | +-+ 1 row in set (0.00 sec)

LPAD (str,n,pad); RPAD (str,n,pad) fills the leftmost or rightmost str with the string pad until it is n characters long (n is greater than the length of str, otherwise it is not padded but intercepted. The LPAD (str,n,pad) and RPAD (str,n,pad) functions: populate the leftmost and rightmost str with the string pad until the length is n strings. Mysql > SELECT lpad ('beijing',10,'123') +-- + | lpad ('beijing',10,'123') | +-- + | 123beijing | +-+ 1 row in set (0.00 sec) mysql > SELECT rpad (' beijing',10,'123') +-- + | rpad ('beijing',10,'123') | +-+ | beijing123 | +-+ 1 row in set (0.00 sec)

LTRIM (str); RTRIM (str) remove the spaces on the left and right side of the string str LTRIM (str) and RTRIM (str) functions: remove the spaces on the left and right side of the string str. Mysql > SELECT ltrim ('| wo |'), rtrim ('| ni |') +-+ | ltrim ('| wo |') | rtrim ('| ni |') | +-+-+ | | wo | | ni | | +- -+-+ 1 row in set (0.00 sec)

REPEAT (str,x) returns the str repeat x times REPEAT (str,x) function: returns the result of str repeat x times. Mysql > SELECT repeat ('beijing',3); +-- + | repeat (' beijing',3) | +-- + | beijingbeijingbeijing | +-+ 1 row in set (0.00 sec)

REPLACE (str,a,b) replaces all occurrences of string a REPLACE (str,a,b) function in string str with string b: replaces all occurrences of string an in string str with string b.

Mysql > SELECT replace ('beijing','bei','nan') +-- + | replace ('beijing','bei' 'nan') | +-+ | nanjing | +-+ 1 row in set (0.00 sec) STRCMP (S1 STRCMP S2) compare the strings S1 and S2 S2) function: compares the ASCII code values of the strings S1 and S2. If S1 is smaller than S2, then-1 is returned, equality returns 0, which is greater than 1.mysql > SELECT strcmp ('axiombinomial'), strcmp ('baked daidjoudjob`), strcmp (' cantileveraged temperb`). +-+ | strcmp ('axiomanagemagenb') | strcmp (' breadthecombind.') | strcmp ('c') 'b') | +-+ |-1 | 0 | 1 | +-+- -+-+ 1 row in set (0.00 sec)

TRIM (str) the space TRIM (str) function that removes the end and head of a string: removes the spaces at the beginning and end of the target string. Mysql > SELECT trim ('ab'); +-+ | trim ('ab') | +-+ | ab | +-+ 1 row in set (0.00 sec)

SUBSTRING (str,x,y) returns a string of y characters from the x-th position in the string str. SUBSTRING (str,x,y) function: returns a string of y characters from the x-th position in the string str. Mysql > SELECT substring ('beijing2017',8,4) +-- + | substring ('beijing2017',8,4) | +-+ | 2017 | +-+ 1 row in set (0.00 sec)