MYSQL functions Daquan (i)

MySQL automatically transforms numbers into strings when necessary, and vice versa:

Mysql> Select 1+ "1"; -2 mysql> SELECT CONCAT (2, ' test '); ' 2 test '

If you want to explicitly transform a number into a string, pass it as an argument 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 only affects comparisons.

String functions

ASCII (str)
Returns the ASCII value of the first character of the string str (str is empty when the string returns 0)
Mysql> Select ASCII (' 2 ');
50
Mysql> Select ASCII (2);
50
Mysql> Select ASCII (' dete ');
100

ORD (str)
If the string str first is a single byte, returns the same value as the ASCII () function.
If it is a multibyte character, return in format (first byte ASCII code) *256+ (second byte ASCII code)) [*256+third byte ASCII code ...]
Mysql> Select ORD (' 2 ');
50

CONV (N,from_base,to_base)
Converts the number n into a string return (NULL is returned if any argument is null, the binary range is 2-36, and n is the unsigned number if to_base is negative), and 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 (+ + ' + ' +0xa,10,10);
' 40 '

BIN (N)
Convert N to binary value and return with string (n is bigint number, equivalent to conv (n,10,2))
Mysql> Select BIN (12);
' 1100 '

OCT (N)
Convert N to octal value and return with string (n is bigint number, equivalent to conv (n,10,8))
Mysql> Select OCT (12);
' 14 '

HEX (N)
Convert N to hex and return with string (n is bigint number, equivalent to conv (n,10,16))
Mysql> Select HEX (255);
' FF '

CHAR (N,...)
Returns the parameter n,... A string of corresponding ASCII code characters (the parameter is n,... is a sequence of numbers, and null values are skipped)
Mysql> Select CHAR (77,121,83,81, ' 76 ');
' MySQL '
Mysql> Select CHAR (77,77.3, ' 77.3 ');
' MMM '

CONCAT (STR1,STR2,...)
The parameter is linked to a long string and returned (null if any argument is null)
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 the length of the string str (calculated once for multibyte character Char_length only)
Mysql> Select LENGTH (' text ');
4
Mysql> Select Octet_length (' text ');
4

LOCATE (SUBSTR,STR)
POSITION (substr in str)
Returns the string substr the first occurrence of the string str (str returns 0 if it does not contain substr)
Mysql> Select LOCATE (' Bar ', ' Foobarbar ');
4
Mysql> Select LOCATE (' Xbar ', ' foobar ');
0

LOCATE (Substr,str,pos)
Returns the position of the first occurrence of the string substr in the POS position of the string str (str does not contain substr when it returns 0)
Mysql> Select LOCATE (' Bar ', ' Foobarbar ', 5);
7

INSTR (STR,SUBSTR)
Returns the string substr the first occurrence of the string str (str returns 0 if it does not contain substr)
Mysql> Select INSTR (' Foobarbar ', ' Bar ');
4
Mysql> Select INSTR (' Xbar ', ' foobar ');
0

Lpad (STR,LEN,PADSTR)
Fills str with string padstr until the string length is Len and returns
Mysql> Select Lpad (' Hi ', 4, '?? ');
'?? Hi

Rpad (STR,LEN,PADSTR)
Fills the right end of Str with a string padstr until the string length is Len and returns
Mysql> Select Rpad (' Hi ', 5, '? ');
' Hi??? '

Left (Str,len)
Returns the left Len character of a string str
Mysql> Select Left (' Foobarbar ', 5);
' Fooba '

Right (Str,len)
Returns the right-side Len character of a string str
Mysql> Select Right (' Foobarbar ', 4);
' Rbar '

SUBSTRING (Str,pos,len)
SUBSTRING (str from POS for Len)
MID (Str,pos,len)
Returns the position of the string str at POS from Len characters (the ugly syntax using from IS ANSI SQL92 standard)
Mysql> Select SUBSTRING (' quadratically ', 5,6);
' Ratica '

SUBSTRING (Str,pos)
SUBSTRING (str from POS)
Returns a substring of the position of the string str from POS
Mysql> Select SUBSTRING (' quadratically ', 5);
' Ratically '
Mysql> Select SUBSTRING (' Foobarbar ' from 4);
' Barbar '

Substring_index (Str,delim,count)
Returns the substring from the count of the string str that appears after the delimiter Delim (the left end is returned when Count is positive, otherwise the right terminal string is returned)
Mysql> Select Substring_index (' www.mysql.com ', '. ', 2);
' Www.mysql '
Mysql> Select Substring_index (' www.mysql.com ', '. ',-2);
' MySQL.com '

LTRIM (str)
Returns the string with the left space removed str
Mysql> Select LTRIM (' barbar ');
' Barbar '

RTRIM (str)
Returns the string with the right space removed str
Mysql> Select RTRIM (' Barbar ');
' Barbar '

TRIM ([[BOTH | Leading | TRAILING] [REMSTR] from] str)
Returns the prefix or suffix remstr the deleted string str (position parameter default Both,remstr default value is a space)
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 '

SOUNDEX (str)
Returns a string that sounds like "roughly the same" to a string with the same string, non-alphanumeric characters are ignored, and letters outside A-Z are used as vowels.
Mysql> Select SOUNDEX (' Hello ');
' H400 '
Mysql> Select SOUNDEX (' quadratically ');
' Q36324 '

SPACE (N)
Returns a string consisting of n space characters
Mysql> Select SPACE (6);
""

REPLACE (STR,FROM_STR,TO_STR)
Replaces substrings in string str with string To_str from_str and returns
Mysql> Select REPLACE (' www.mysql.com ', ' w ', ' Ww ');
' WwWwWw.mysql.com '

REPEAT (Str,count)
Returns a string that is linked by Count string Str (returns an empty string when null,count<=0 is returned when any parameter is null)
Mysql> Select REPEAT (' MySQL ', 3);
' Mysqlmysqlmysql '

REVERSE (str)
Reverses the character order of the string str and returns
Mysql> Select REVERSE (' abc ');
' CBA '

INSERT (STR,POS,LEN,NEWSTR)
The string str is replaced with a string newstr by a substring of position pos, Len characters long, and returns
Mysql> Select INSERT (' Quadratic ', 3, 4, ' what ');
' Quwhattic '

ELT (N,STR1,STR2,STR3,...)
Returns the nth string (n is less than 1 or greater than the number of arguments returns NULL)
Mysql> Select ELT (1, ' ej ', ' Heja ', ' Hej ', ' foo ');
' EJ '
Mysql> Select ELT (4, ' ej ', ' Heja ', ' Hej ', ' foo ');
' Foo '

FIELD (STR,STR1,STR2,STR3,...)
Returns the ordinal of the nth string after str equals (if STR is not found returns 0)
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)
Returns the ordinal of STR in the string set Strlist (any parameter is NULL, returns NULL if STR does not find return 0, parameter 1 contains "," when working abnormally)
mysql> SELECT find_in_set (' B ', ' a,b,c,d ');
2

Make_set (BITS,STR1,STR2,...)
Convert the number of the parameter 1 to binary, if the bits of a position equals 1, the string of the corresponding position is selected in the string set and returned (the null string 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 ');
""

Export_set (Bits,on,off,[separator,[number_of_bits])
Sets the set of strings by bits, inserting off (separator default ",", number_of_bits parameter is not longer than 0 when the insertion string is equal to 1 o'clock)
Mysql> Select Export_set (5, ' Y ', ' N ', ', ', 4)
Y,n,y,n

LCASE (str)
LOWER (str)
Returns the lowercase string str
Mysql> Select LCASE (' quadratically ');
' Quadratically '

UCASE (str)
UPPER (str)
Returns the uppercase String str
Mysql> Select UCASE (' quadratically ');
' Quadratically '

Load_file (file_name)
Reads the file and returns the file contents as a string (the file cannot be found, the path is incomplete, no permissions, and the length is greater than Max_allowed_packet will return null)
mysql> UPDATE table_name SET blob_column=load_file ("/tmp/picture") WHERE id=1;

Mathematical functions

ABS (N)
Returns the absolute value of n
Mysql> Select ABS (2);
2
Mysql> Select ABS (-32);
32

Sign (N)
Returns the symbol for the parameter (1, 0, or 1)
Mysql> Select sign (-32);
-1
Mysql> Select sign (0);
0
Mysql> Select sign (234);
1

MOD (N,M)
Modulo operation, returns the remainder of n being removed by M (same as% operator)
Mysql> Select MOD (234, 10);
4
Mysql> Select 234% 10;
4
Mysql> Select MOD (29,9);
2

Floor (N)
Returns the maximum integer value not greater than n
Mysql> Select Floor (1.23);
1
Mysql> Select Floor (-1.23);
-2

CEILING (N)
Returns the smallest integer value not less than n
Mysql> Select CEILING (1.23);
2
Mysql> Select CEILING (-1.23);
-1

ROUND (N,D)
Returns the rounding value of N, leaving the D decimal number (the default value of D is 0)
Mysql> Select ROUND (-1.23);
-1
Mysql> Select ROUND (-1.58);
-2
Mysql> Select ROUND (1.58);
2
Mysql> Select ROUND (1.298, 1);
1.3
Mysql> Select ROUND (1.298, 0);
1

EXP (N)
Returns the n-th square of the value E (the base of the natural logarithm)
Mysql> Select EXP (2);
7.389056
Mysql> Select EXP (-2);
0.135335

LOG (N)
Returns the natural logarithm of n
Mysql> Select LOG (2);
0.693147
Mysql> Select LOG (-2);
, NULL

LOG10 (N)
Returns the logarithm of n at base 10
Mysql> Select LOG10 (2);
0.301030
Mysql> Select LOG10 (100);
2.000000
Mysql> Select LOG10 (-100);
, NULL

POW (x, y)
POWER (x, y)
The Y-power of the return value X
Mysql> Select POW (2,2);
4.000000
Mysql> Select POW (2,-2);
0.250000

SQRT (N)
Returns the square root of a non-negative n
Mysql> Select SQRT (4);
2.000000
Mysql> Select SQRT (20);
4.472136

PI ()
return pi
Mysql> select PI ();
3.141593

COS (N)
Returns the cosine value of n
Mysql> Select COS (PI ());
-1.000000

SIN (N)
Returns the sinusoidal value of n
Mysql> Select SIN (PI ());
0.000000

TAN (N)
Returns the tangent value of n
Mysql> Select TAN (PI () +1);
1.557408

ACOS (N)
Returns the n inverse cosine (n is the cosine value, in the range 1 to 1, otherwise null is returned)
Mysql> Select ACOS (1);
0.000000
Mysql> Select ACOS (1.0001);
, NULL
Mysql> Select ACOS (0);
1.570796

ASIN (N)
Returns n Inverse sine value
Mysql> Select ASIN (0.2);
0.201358
Mysql> SE

MYSQL functions Daquan (i)