For operations on string positions, the first position is marked as 1.
-
-
ASCII(str)
-
-
Returns the
str
ASCII code value of the leftmost character of the string. If
str
It is an empty string, return
0
. If
str
Yes
NULL
, return
NULL
.
Mysql> Select ASCII (' 2 ');
-> 50
Mysql> Select ASCII (2);
-> 50
Mysql> Select ASCII (' DX ');
-> 100
You can also see the Ord () function.
-
-
ORD(str)
-
-
If the leftmost character of the string str is a multibyte character, the
((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]
multibyte character code is returned by using the ASCII code value of the character returned in the format. If the leftmost character is not a multibyte character. Returns
ASCII()
the same value returned by the function.
Mysql> Select ORD (' 2 ');
-> 50
-
-
CONV(N,from_base,to_base)
-
The
-
transforms a number between different numeric bases. Returns the string number of the number
n
, from the
from_base
Base transformation to
to_base
Base, if any parameter is
null
, returns
null
. The parameter
n
is interpreted as an integer, but can be specified as an integer or a string. The minimum base is the
2
and the largest base is
36
. If the
to_base
is a negative number,
n
is considered to be a signed Otherwise, the
n
is treated as an unsigned number. The
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+ "+" +0xa,10,10);
-> ' A '
-
-
BIN(N)
-
-
returns a string representation of a binary value, which
N
N
is a long integer (
BIGINT
) number, which is equivalent to
CONV(N,10,2)
. If
N
Yes
NULL
, return
NULL
.
Mysql> Select BIN (12);
-> ' 1100 '
-
-
OCT(N)
-
-
returns the representation of a string representing the octal value, which
N
N
is a long integer number, which is equivalent to
CONV(N,10,8)
. If
N
it is null, return
NULL
.
Mysql> Select OCT (12);
-> ' 14 '
-
-
HEX(N)
-
-
returns the
N
representation of a string of hexadecimal values, here
N
is a long integer (
BIGINT
) number, which is equivalent to
CONV(N,10,16)
. If
N
Yes
NULL
, return
NULL
.
Mysql> Select HEX (255);
-> ' FF '
-
-
CHAR(N,...)
-
-
CHAR()
interprets parameters as integers and returns a string of ASCII code characters for these integers.
NULL
value is skipped.
Mysql> Select CHAR (77,121,83,81, ' 76 ');
-> ' MySQL '
Mysql> Select CHAR (77,77.3, ' 77.3 ');
-> ' MMM '
-
-
CONCAT(str1,str2,...)
-
-
Returns a string from the argument's link . If any argument is
NULL
, return
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 '
-
-
LENGTH(str)
-
-
-
-
OCTET_LENGTH(str)
-
-
-
-
CHAR_LENGTH(str)
-
-
-
-
CHARACTER_LENGTH(str)
-
-
returns
str
the length of the string.
Mysql> Select LENGTH (' text ');
-> 4
Mysql> Select Octet_length (' text ');
-> 4
Note that for multibyte characters, its char_length () is evaluated only once.
-
-
LOCATE(substr,str)
-
-
-
-
POSITION(substr IN str)
-
-
Returns the
substr
position of the substring in the
str
first occurrence of the string, if
substr
not
str
inside
0
.
Mysql> Select LOCATE (' Bar ', ' Foobarbar ');
-> 4
Mysql> Select LOCATE (' Xbar ', ' foobar ');
-> 0
The function is multiple-byte reliable.
-
-
LOCATE(substr,str,pos)
-
-
returns the
substr
position of the substring at the
str
first occurrence of the string, starting at the position
pos
. If
substr
not in the
str
inside, return
0
.
Mysql> Select LOCATE (' Bar ', ' Foobarbar ', 5);
-> 7
This function is multibyte-reliable.
-
-
INSTR(str,substr)
-
-
returns the
substr
str
position of the first occurrence of a substring in a string. This is the same as having 2 parameter forms
LOCATE()
, except that the parameters are reversed.
Mysql> Select INSTR (' Foobarbar ', ' Bar ');
-> 4
Mysql> Select INSTR (' Xbar ', ' foobar ');
-> 0
This function is multibyte-reliable.
-
-
LPAD(str,len,padstr)
-
-
returns
str
a string, and the left
padstr
is filled with a string until
str
it is
len
a character long.
Mysql> Select Lpad (' Hi ', 4, '?? ');
-> '?? Hi
-
-
RPAD(str,len,padstr)
-
-
Returns
str
a string that
padstr
is filled with a string until
str
it is
len
a character long.
Mysql> Select Rpad (' Hi ', 5, '? ');
-> ' Hi??? '
-
-
LEFT(str,len)
-
-
returns
str
the leftmost character of
len
a string.
Mysql> Select Left (' Foobarbar ', 5);
-> ' Fooba '
The function is multiple-byte reliable.
-
-
RIGHT(str,len)
-
-
Returns
str
the rightmost character of
len
a string
。
Mysql> Select Right (' Foobarbar ', 4);
-> ' Rbar '
The function is multiple-byte reliable.
-
-
SUBSTRING(str,pos,len)
-
-
-
-
SUBSTRING(str FROM pos FOR len)
-
-
-
-
MID(str,pos,len)
-
-
str
returns a substring of a character from a string
len
, starting at the position
pos
.
FROM
the variant form used is the ANSI SQL92 syntax.
Mysql> Select SUBSTRING (' quadratically ', 5,6);
-> ' Ratica '
The function is multiple-byte reliable.
-
-
SUBSTRING(str,pos)
-
-
-
-
SUBSTRING(str FROM pos)
-
-
str
returns a substring from the start position of the string
pos
.
Mysql> Select SUBSTRING (' quadratically ', 5);
-> ' ratically '
Mysql> Select SUBSTRING (' Foobarbar ' from 4);
-> ' Barbar '
The function is multiple-byte reliable.
-
-
SUBSTRING_INDEX(str,delim,count)
-
-
returns a substring after the first occurrence of a delimiter in a string
str
count
的
delim
. If this
count
is a positive number, return the last separator to all characters to the left (from the left). If
count
it is a negative number, returns the last separator to all characters on the right (from 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.
-
-
LTRIM(str)
-
-
returns a string that deletes its preceding space character
str
.
Mysql> Select LTRIM (' Barbar ');
-> ' Barbar '
-
-
RTRIM(str)
-
-
returns the string whose trailing space character has been deleted
str
.
Mysql> Select RTRIM (' Barbar ');
-> ' Barbar '
This function is reliable for multiple bytes.
-
-
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
-
-
Returns
str
a string with all of its
remstr
prefixes or suffixes removed. If no modifier
BOTH
,
LEADING
or
TRAILING
given,
BOTH
is assumed. If
remstr
it 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 '
This function is reliable for multiple bytes.
-
-
SOUNDEX(str)
-
-
Returns
str
a homonym string. The 2 strings that sound "roughly the same" should have the same homonym string. A "standard" homonym string length is 4 characters, but the
SOUNDEX()
function returns an arbitrarily long string. You can use the results to
SUBSTRING()
get a "standard" of the homophonic string. All non-numeric alphabetic characters are ignored in the given string. All characters outside A-Z are treated as vowels.
Mysql> Select SOUNDEX (' Hello ');
-> ' H400 '
Mysql> Select SOUNDEX (' quadratically ');
-> ' Q36324 '
-
-
SPACE(N)
-
-
returns
N
a string consisting of a space character.
Mysql> Select space (6);
-> '
-
-
REPLACE(str,from_str,to_str)
-
-
Returns
str
a string in which
from_str
all occurrences of the string are replaced by strings
to_str
.
Mysql> Select REPLACE (' www.mysql.com ', ' w ', ' Ww ');
-> ' WwWwWw.mysql.com '
This function is reliable for multiple bytes.
-
-
REPEAT(str,count)
-
-
returns the duplicate
count
str
A string consisting of a string of times. If
count <= 0
, returns an empty string. If
str
or
count
Yes
NULL
, return
NULL
.
Mysql> Select REPEAT (' MySQL ', 3);
-> ' Mysqlmysqlmysql '
-
-
REVERSE(str)
-
-
returns a string that reverses the order of characters
str
.
Mysql> Select REVERSE (' abc ');
-> ' CBA '
This function is reliable for multiple bytes.
-
-
INSERT(str,pos,len,newstr)
-
-
returns a string in which the substring
str
at the
pos
beginning of the position and the substring of
len
characters are replaced by strings
newstr
.
Mysql> Select INSERT (' Quadratic ', 3, 4, ' What ');
-> ' quwhattic '
This function is reliable for multiple bytes.
-
-
ELT(N,str1,str2,str3,...)
-
-
If
N
=
1
, return
str1
, if
N
=
2
, return
str2
, and so on.
N
returns if
1
the number of arguments is less than or greater than
NULL
.
ELT()
is a
FIELD()
reverse operation.
Mysql> Select ELT (1, ' ej ', ' Heja ', ' Hej ', ' foo ');
-> ' EJ '
Mysql> Select ELT (4, ' ej ', ' Heja ', ' Hej ', ' foo ');
-> ' foo '
-
-
FIELD(str,str1,str2,str3,...)
-
-
Returns
str
the
str1
index of,,, the
str2
str3
...
list. If
str
not found, return
0
.
FIELD()
is a
ELT()
reverse operation.
Mysql> Select FIELD (' EJ ', ' Hej ', ' ej ', ' Heja ', ' Hej ', ' foo ');
-> 2
Mysql> Select FIELD (' fo ', ' Hej ', ' ej ', ' Heja ', ' Hej ', ' foo ');
-> 0
-
-
FIND_IN_SET(str,strlist)
-
-
if the string
str
in a table made up of
n Child Strings
Font face= "Nsimsun" >strlist , returns a
1
to
". If the first argument is a constant string and the second argument is a column of type set
, find_in_ The SET ()
function is optimized to use bit arithmetic! If the str
is not in strlist
inside or if The font face= "Nsimsun" >strlist
is an empty string that returns 0
. If any one of the parameters is null
, return null
. If the first argument contains a , the function will not work correctly. mysql> SELECT find_in_set (' B ', ' a,b,c,d ');
-> 2
-
-
MAKE_SET(bits,str1,str2,...)
-
-
returns a collection containing the “,”字符分隔的子串组成的一个 字符串),由相应的位在
bits
集合中的的字符串组成。
str1
对应于位0,
str2
对 应位1,等等。在
str1
,
str2
,
...
中 的
NULL
串不添加到结果中。
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');
-> ''
-
-
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.
Mysql> Select Export_set (5, ' Y ', ' N ', ', ', ', ', ', ', 4)
-
-
LCASE(str)
-
-
-
-
LOWER(str)
-
-
returns a string
str
that 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.
Mysql> Select LCASE (' quadratically ');
-> ' quadratically '
-
-
UCASE(str)
-
-
-
-
UPPER(str)
-
-
Returns a string
str
that 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.
Mysql> Select UCASE (' Hej ');
-> ' HEJ '
This function is reliable for multiple bytes.
-
-
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 if the file does not exist or because one of the above reasons cannot be read out
NULL
.
Mysql> UPDATE table_name
SET blob_column=load_file ("/tmp/picture")
WHERE id=1;
If you want to explicitly transform a number into a string, pass it as a parameter to CONCAT()
.
If a string function provides a binary string as a parameter, the resulting string is also a binary string. A number that is transformed into a string is treated as a binary string. This affects only the comparison.