MySQL string operation correlation function Usage Summary

Tag: cal. com specifies a numeric localhost character string local operation

List of simple examples of functional usage

Function	Function	Usage	Example
Left ()	Returns a substring of the corresponding length from the left edge of the string	Left (str, length)	Mysql> Select Left (' VSSF ', 3); +----------------+| Left (' VSSF ', 3) |+----------------+| VSS |+----------------+1 row in Set (0.00 sec)
Right ()	Returns a substring of the appropriate length from the right edge of the string	Rigth (Str,length)	Mysql> Select Right (' VSSF ', 1); +-----------------+| Right (' VSSF ', 1) |+-----------------+| F |+-----------------+1 row in Set (0.00 sec)
substring ()	returns the appropriate substring based on the starting position and length	substring (str,pos,length)	mysql> Select substring (' hello ', +); +------------------------+| substring (' hello ', |+------------------------+| h                      |+------------------------+1 row in Set (0.0 0 sec)  mysql> Select substring (' Hello ', -4,1); +-------------------------+| substring (' hello ', -4,1) |+-------------------------+| e                       |+-------------------------+1 row in S ET (0.00 sec)
Substring_index ()	Takes a character (Delim) as the selection point, capturing all the characters of that character. Count is a positive number when the character is changed from left to right. If Delim is not found, the entire string is returned	Substring_index (Str,delim,count)	Mysql> Select Substring_index (' Hello.com.index ', '. ', 1); +------------------------------------------+| Substring_index (' Hello.com.index ', '. ', 1) |+------------------------------------------+| Hello |+------------------------------------------+1 row in Set (0.00 sec)
ASCII ()	Returns the ASCII value of the leftmost character of the string str, returns 0 if it is an empty string, or null if it is null. Input is integer and also treated as String	ASCII (STR)	Mysql> Select ASCII (' 2 '), select ASCII (2), +------------+|         ASCII (' 2 ') |+------------+| |+------------+1 Row in Set (0.00 sec) +----------+|       ASCII (2) |+----------+| |+----------+1 Row in Set (0.00 sec)
Length ()	Calculating string Lengths	Length (str)	Mysql> Select Length (User ()); +----------------+|             Length (User ()) |+----------------+| |+----------------+1 Row in Set (0.00 sec)
Locate ()	Returns the substring substr where the first occurrence of the string Str begins, starting at position pos. If SUBSTR is not inside STR, return 0	LOCATE (Substr,str,pos)	Mysql> Select locate (' 123 ', ' 141234 ', 2); +--------------------------+|                        Locate (' 123 ', ' 141234 ', 2) |+--------------------------+| 3 |+--------------------------+1 row in Set (0.00 sec)
InStr ()	Returns the position of the first occurrence of a substring substr in the string str. This is the same as the locate () with 2 parameters, except that the parameters are reversed	INSTR (STR,SUBSTR)	Mysql> Select InStr (' 12345 ', ' 123 '); +----------------------+|                    InStr (' 12345 ', ' 123 ') |+----------------------+| 1 |+----------------------+1 row in Set (0.00 sec)
Lpad ()	return string str, left with string padstr fill until str is Len characters long	Lpad (STR,LEN,PADSTR)	Mysql> Select Lpad (' 123 ', ten, ' 456 '); +----------------------+| Lpad (' 123 ', ten, ' 456 ') |+----------------------+| 4564564123 |+----------------------+1 row in Set (0.00 sec)
LTrim ()	Returns the string whose front space character was deleted str	LTRIM (str)	Mysql> Select LTrim (' 121 '); +-----------------+| LTrim (' 121 ') |+-----------------+| 121 |+-----------------+1 row in Set (0.00 sec)
RTrim ()	Returns the string whose trailing space character was deleted by Str	RTRIM (str)	Mysql> Select RTrim (' 121 '); +----------------+| RTrim (' 121 ') |+----------------+| 121 |+----------------+1 row in Set (0.00 sec)
UCase ()	Returns the string str, which changes all characters to uppercase based on the current character set mapping (the default is Iso-8859-1 Latin1). This function is reliable for multi-byte	UCASE (str)	Mysql> Select UCASE (' SAE '); +--------------+| UCASE (' SAE ') |+--------------+| SAE |+--------------+1 row in Set (0.00 sec)
LCase ()	Returns the string str, which changes 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.	LCASE (str)	Mysql> Select LCASE (' SS '); +-------------+| LCASE (' SS ') |+-------------+| SS |+-------------+1 Row in Set (0.00 sec)
Make_set ()	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.	Make_set (BITS,STR1,STR2,...)	Mysql> Select Make_set (1|2,user,pass) from ctf;+-------------------------+| Make_set (1|2,user,pass) |+-------------------------+| 1admin,11 | | 4admin,pass | | admin123,123 | | Admin,test |+-------------------------+4 rows in Set (0.00 sec)
Reverse ()	Returns a string that reverses the order of characters Str	REVERSE (str)	Mysql> Select REVERSE (' 123 '); +----------------+| REVERSE (' 123 ') |+----------------+| 321 |+----------------+1 row in Set (0.00 sec)
Space ()	Returns a string consisting of n space characters	SPACE (N)	Mysql> Select Concat (1,space (2), 3); +----------------------+| Concat (1,space (2), 3) |+----------------------+| 1 3 |+----------------------+1 row in Set (0.00 sec)
Soundex	Returns an homophonic string of str. The 2 strings that sound "roughly the same" should have the same homophonic string. A "standard" of the same character string length is 4 characters, but the SOUNDEX () function returns an arbitrarily long string. You can use SUBSTRING () on the results to get a "standard" string of sounds. All non-numeric alphabetic characters are ignored in the given string. All characters outside A-Z are used as vowels.	SOUNDEX (str)	Mysql> Select Soundex (' Hello '); +------------------+| Soundex (' hello ') |+------------------+| H400 |+------------------+1 row in Set (0.00 sec)
Trim ()	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.	TRIM ([[BOTH | Leading | TRAILING] [REMSTR] from] str)	mysql> Select Trim (' DSF '), +---------------+| trim (' DSF ') |+---------------+| dsf        &NB Sp  |+---------------+1 Row in Set (0.00 sec)  mysql> Select trim (Leading ' x ' from ' xxxbarxxx '); +-------------- ----------------------+| Trim (Leading ' x ' from ' xxxbarxxx ') |+------------------------------------+| barxxx                             |+---------- --------------------------+1 Row in Set (0.00 sec)  mysql> Select trim (trailing ' x ' from ' xxxbarxxx '); +---------- ---------------------------+| Trim (trailing ' x ' from ' xxxbarxxx ') |+-------------------------------------+| xxxbar                            |+--------- ----------------------------+1 Row in Set (0.00 sec)
Relace ()	Returns the string str, whose string from_str all occurrences are replaced by the string to_str	REPLACE (STR,FROM_STR,TO_STR)	mysql> Select replace (' www.ok.com ', ' w ', ' Q '); +-------------------------------+| Replace (' www.ok.com ', ' w ', ' Q ') |+-------------------------------+| qqq.ok.com |+-------------------------------+1 row in Set (0.00 sec)
Repeat ()	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.	REPEAT (Str,count)	Mysql> Select repeat (' 223 ', 2); +-----------------+| Repeat (' 223 ', 2) |+-----------------+| 223223 |+-----------------+1 row in Set (0.00 sec)
Insert ()	returns the string str, the substring starting at position pos, and Len characters long substring by string newstr instead of	Insert (STR,POS,LEN,NEWSTR)	mysql> Select Insert (' DSFHDSA ', 3,4, ' ww '), +----------------------------+| insert (' DSFHDSA ', 3,4, ' ww ') |+----- -----------------------+| dswwa                      |+----------------------------+1 row in Set (0.00 sec)
elt ()	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 arguments, NULL is returned. ELT () is a field () inverse Operation	elt (n,str1,str2,str3,...)	mysql> Select ELT (1,user (), version ()), +-------------------------+| elt (1,user (), version ()) |+-------------- -----------+| [email protected]          |+-------------------------+1 row in Set (0.00 sec)  mysql& Gt Select ELT (2,user (), version ()), +-------------------------+| ELT (2,user (), version ()) |+-------------------------+| 5.5.47                  |+-------------------------+1 row in Set (0.00 sec)
Field ()	Return str in str1, str2, STR3, ... The index of the manifest. If STR is not found, return 0. FIELD () is an ELT () inverse operation	FIELD (STR,STR1,STR2,STR3,...)	mysql> Select field (' V ', ' he ', ' hekl '); +------------------------+|                      Field (' V ', ' he ', ' hekl ') |+------------------------+| 0 |+------------------------+1 row in Set (0.00 sec)
Mid ()	Returns the corresponding substring based on the starting position and length, as with substring	MID (ColumnName, Start [, Length])	Mysql> Select mid (' Sfsdf ', 2,1); +------------------+| Mid (' Sfsdf ', 2,1) |+------------------+| F |+------------------+1 row in Set (0.00 sec) mysql> Select mid (' Sfsdf ', 2); +----------------+| Mid (' Sfsdf ', 2) |+----------------+| FSDF |+----------------+1 row in Set (0.00 sec)
Concat ()	The connection parameter, such as STR1,STR2, produces a string that returns null if the argument contains null	CONCAT (STR1,STR2,...)	Mysql> Select Concat (' 1 ', ' 2 '); +-----------------+| Concat (' 1 ', ' 2 ') |+-----------------+| |+-----------------+1 Row in Set (0.00 sec) mysql> Select concat (' 1 ', ' 2 ', null); +----------------------+ | Concat (' 1 ', ' 2 ', null) |+----------------------+| NULL |+----------------------+1 row in Set (0.00 sec)
CONCAT_WD ()	The connection parameter produces a string that can be custom delimited, has null in the parameter, does not return NULL, and returns the result of the other string connection normally	Concat_ws (SEPARATOR,STR1,STR2,...)	Mysql> Select Concat_ws (', ', ' 2 ', ' 3 ', null); +-----------------------------+| Concat_ws (', ', ' 2 ', ' 3 ', null) |+-----------------------------+| 2,3 |+-----------------------------+1 row in Set (0.00 sec)
Group_concat ()	The connection parameter produces a string that can be defined as a delimiter, can be de-weighed, sortable	Group_concat ([DISTINCT] field to connect [order by Asc/desc sort field] [Separator ' delimiter '])	Mysql> Select Group_concat (user,pass) from ctf;+---------------------------------------------+| Group_concat (user,pass) |+---------------------------------------------+| 1admin11,4adminpass,admin123123,admintest |+---------------------------------------------+1 row in Set (0.00 sec)

MySQL string operation correlation function Usage Summary