MySQL functions Daquan

String functions

1.ASCII (str)
Returns the ASCII value of the first character of the string str (str is empty when the string returns 0)

SELECT ASCII (' 2 ');

->50

2.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 asciicode ...]

SELECT ORD (' 2 ');

->50

3.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.

SELECT CONV (' A ', 16, 2);

->1010

SELECT CONV (' A ', 16, 10);

->10

SELECT CONV (' A ', 16, 8);

->12

4.BIN (N)

Convert N to binary value and return with string (n is bigint number, equivalent to conv (n,10,2))

SELECT BIN (12);

->1100

5.OCT (N)

Convert N to octal value and return with string (n is bigint number, equivalent to conv (n,10,8))

SELECT OCT (12);

->14

6.HEX (N)

Convert N to hex and return with string (n is bigint number, equivalent to conv (n,10,16))

SELECT HEX (255);

->ff

7.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)

SELECT CHAR (77,121,83,81, ' 76 ');

->mysql

Select CHAR (77,77.3, ' 77.3 ');

->mmm

8.CONCAT (STR1,STR2,...)

The parameter is linked to a long string and returned (null if any argument is null)

SELECT CONCAT (' My ', ' S ', ' QL ');

->mysql

SELECT CONCAT (' My ', NULL, ' QL ');

->null

SELECT CONCAT (14.3);

->14.3

9.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)

SELECT LENGTH (' Textverylong Chinese ');

SELECT octet_length (' Textverylong Chinese ');

->18

SELECT character_length (' Textverylong Chinese ');

SELECT char_length (' Textverylong Chinese ');

->14

10.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)

Select LOCATE (' Bar ', ' Foobarbar ');

SELECT POSITION (' Bar ' in ' Foobarbar ');

->4

11.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)

SELECT LOCATE (' Bar ', ' Foobarbar ', 5);

->7

12.INSTR (STR,SUBSTR)

Returns the string substr the first occurrence of the string str (str returns 0 if it does not contain substr)

SELECT INSTR (' Foobarbar ', ' Bar ');

->4

SELECT INSTR (' Xbar ', ' foobar ');

->0

13.LPAD (STR,LEN,PADSTR)

Fills str with string padstr until the string length is Len and returns

SELECT lpad (' Hi ', 4, '?? ');

->?? Hi

14.RPAD (STR,LEN,PADSTR)

Fills the right end of Str with a string padstr until the string length is Len and returns

SELECT rpad (' Hi ', 5, '? ');

->hi???

15.LEFT (Str,len)

Returns the left Len character of a string str

SELECT left (' Foobarbar ', 5);

->fooba

16.RIGHT (Str,len)

Returns the right-side Len character of a string str

Select Right (' Foobarbar ', 4);

->rbar

17.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)

SELECT SUBSTRING (' quadratically ', 5,6);

SELECT MID (' quadratically ', 5,6);

->ratica

18.SUBSTRING (Str,pos), SUBSTRING (str from POS)

Returns a substring of the position of the string str from POS

SELECT SUBSTRING (' quadratically ', 5);

->ratically

19.substring_index (Str,delim,count)

Returns the substring that appears after the delimiter Delim from the count of the string str (count is positive when you look from left to right and returns to the left side, otherwise the right-to-left lookup and return right terminal string)

SELECT substring_index (' www.mysql.com ', '. ', 2);

->www.mysql

SELECT substring_index (' www.mysql.com ', '. ',-2);

->mysql.com

20.LTRIM (str)

Returns the string with the left space removed str

Select LTRIM (' Barbar ');

->barbar

21.RTRIM (str)

Returns the string with the right space removed str

SELECT RTRIM (' Barbar ');

->barbar

22.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)

SELECT TRIM (' Bar ');

->bar

SELECT TRIM (Leading ' x ' from ' xxxbarxxx ');

->barxxx

SELECT TRIM (BOTH ' x ' from ' xxxbarxxx ');

->bar

Select TRIM (TRAILING ' xyz ' from ' barxxyz ');

->barx

23.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.

SELECT SOUNDEX (' Hello ');

->h400

24.SPACE (N)

Returns a string consisting of n space characters

SELECT SPACE (6);

-

25.REPLACE (STR,FROM_STR,TO_STR)

Replaces substrings in string str with string To_str from_str and returns

SELECT REPLACE (' www.mysql.com ', ' w ', ' Ww ');

->wwwwww.mysql.com

26.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)

SELECT REPEAT (' MySQL ', 3);

Mysqlmysqlmysql

27.REVERSE (str)

Reverses the character order of the string str and returns

SELECT REVERSE (' abc ');

->cba

28.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

SELECT INSERT (' Quadratic ', 3, 4, ' what ');

->quwhattic

29.ELT (N,STR1,STR2,STR3,...)

Returns the nth string (n is less than 1 or greater than the number of arguments returns NULL)

SELECT ELT (1, ' ej ', ' Heja ', ' Hej ', ' foo ');

->ej

Select ELT (4, ' ej ', ' Heja ', ' Hej ', ' foo ');

->foo

30.FIELD (STR,STR1,STR2,STR3,...)

Returns the ordinal of the nth string after str equals (if STR is not found returns 0)

SELECT FIELD (' ej ', ' Hej ', ' ej ', ' Heja ', ' Hej ', ' foo ');

->2

31.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)

SELECT Find_in_set (' B ', ' a,b,c,d ');

->2

32.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)

SELECT make_set (1, ' A ', ' B ', ' C ');

->a

SELECT Make_set (1 | 4, ' hello ', ' nice ', ' world ');

->hello,world

SELECT make_set (0, ' a ', ' B ', ' C ');

""

33.export_set (Bits,on,off,[separator,[number_of_bits])

Sets the set of strings by bits, which is inserted off (separator default ",", when the Number_of_bits parameter is used, the actual length of bits is less than number_of_bits when the bit equals 1 o'clock insert string on)

SELECT Export_set (5, ' Y ', ' N ', ', ', 4)

->y,n,y,n

SELECT Export_set (5, ' Y ', ' N ', ', ', 3)

->y,n,y truncation

SELECT Export_set (5, ' Y ', ' N ', ', ', 5)

->y,n,y,n,n complement 0

34.LCASE (str), LOWER (str)

Returns the lowercase string str

SELECT LCASE (' quadratically ');

SELECT LOWER (' quadratically ');

->quadratically

35.UCASE (str), UPPER (str)

Returns the uppercase String str

SELECT UCASE (' quadratically ');

SELECT UPPER (' quadratically ');

->quadratically

36.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)

UPDATE table_name SET blob_column=load_file ("/tmp/picture") WHERE id=1;

Mathematical functions

1.ABS (N)

Returns the absolute value of n

Select ABS (2);

->2

Select ABS (-32);

->32

2.SIGN (N)

Returns the symbol for the parameter (1, 0, or 1)

Select sign (-32)

->-1

Select sign (0)

->0

Select sign (234)

->1

3.MOD (N,M)

Modulo operation, returns the remainder of n being removed by M (same as% operator)

SELECT MOD (234, 10);

->4

Select 234% 10;

->4

4.FLOOR (N)

Returns the maximum integer value not greater than n

Select floor (1.23);

->1

SELECT Floor (-1.23);

->-2

5.CEILING (N)

Returns the smallest integer value not less than n

Select CEILING (1.23);

->2

SELECT CEILING (-1.23);

->-1

6.ROUND (N,D)

Returns the rounding value of N, leaving the D decimal number (the default value of D is 0)

Select ROUND (-1.23);

->-1

Select ROUND (-1.58);

->-2

Select ROUND (1.58);

->2

Select ROUND (1.298, 1);

->1.3

Select ROUND (1.298, 0);

->1

7.EXP (N)

Returns the n-th square of the value E (the base of the natural logarithm)

Select EXP (2);

->7.38905609893065

Select EXP (-2);

->0.1353352832366127

8.LOG (N)

Returns the natural logarithm of n

SELECT LOG (2);

->0.6931471805599453

SELECT LOG (-2);

->null

9.LOG10 (N)

Returns the logarithm of n at base 10

SELECT LOG10 (2);

->0.3010299956639812

Select LOG10 (100);

->2.000000

Select LOG10 (-100);

->null

10.POW (x, y), POWER (x, y)

The Y-power of the return value X

Select POW (2,2);

->4

Select POW (2,-2);

->0.25

11.SQRT (N)

Returns the square root of a non-negative n

SELECT SQRT (4);

->2

SELECT SQRT (-4);

->null

12.PI ()

return pi

Select PI ();

->3.141593

13.COS (N)

Returns the cosine value of n

SELECT COS (PI ());

->-1

14.SIN (N)

Returns the sinusoidal value of n

SELECT SIN (PI ());

->0.000000

15.TAN (N)

Returns the tangent value of n

SELECT TAN (PI () +1);

->1.5574077246549018

16.ACOS (N)

Returns the n inverse cosine (n is the cosine value, in the range 1 to 1, otherwise null is returned)

SELECT ACOS (1);

->0

Select ACOS (1.0001);

->null

Select ACOS (0);

->1.5707963267948966

17.ASIN (N)

Returns n Inverse sine value

SELECT ASIN (0.2);

->0.2013579207903308

Select ASIN (' foo ');

->0

18.ATAN (N)

Returns the inverse tangent value of n

Select ATAN (2);

->1.1071487177940904

Select ATAN (-2);

->-1.1071487177940904

19.atan2 (x, y)

Returns 2 variables x and y of the inverse tangent (similar to the y/x tangent, the symbol determines the quadrant)

SELECT ATAN ( -2,2);

->-0.7853981633974483

Select ATAN (PI (), 0);

->1.570796

20.COT (N)

Returns the cotangent of X

Select COT (12);

->-1.5726734063976893

21.RAND (), RAND (N)

Returns a random floating-point value within a range of 0 to 1.0 (you can use the number n as the initial value)

SELECT RAND ();

->0.10821635193025353

Select RAND (20);

->0.15888261251047497

22.DEGREES (N)

Transforms n from radians to angles and returns

SELECT DEGREES (PI ());

->180

23.RADIANS (N)

Transform n from angle to radians and return

SELECT RADIANS (90);

->1.5707963267948966

24.TRUNCATE (N,D)

Retains the D decimal number of the digit N and returns

Select TRUNCATE (1.223,1);

->1.2

SELECT TRUNCATE (1.999,2);

->1.99

Select TRUNCATE (1.999,0);

->1

25.LEAST (x, y,...)

Returns the minimum value (if the return value is used in an integer (real or size-sensitive string) context or if all parameters are integers (real or size-sensitive strings) then they are compared as integers (real or size-sensitive strings), otherwise by the case-insensitive string is compared)

SELECT LEAST (2,0);

->0

SELECT LEAST (34.0,3.0,5.0,767.0);

->3.0

Select LEAST ("B", "A", "C");

->a

26.GREATEST (x, y,...)

Returns the maximum value (the rest of the same least ())

SELECT greatest (2,0);

->2

Select Greatest (34.0,3.0,5.0,767.0);

->767.0

Select Greatest ("B", "A", "C");

->c

Period Time function

1.DAYOFWEEK (date)

The return date is the day of the week (1= Sunday, 2 = Monday,...... 7= Saturday, ODBC Standard)

SELECT DAYOFWEEK (' 2015-04-07 ');

->3

2.WEEKDAY (date)

The return date is the day of the week (0= Monday, 1 = Tuesday,...... 6= Sunday).

SELECT WEEKDAY (' 2015-04-07 ');

->1

3.DAYOFMONTH (date)

Return date is the day of the January (within the range of 1 to 31)

SELECT dayofmonth (' 2015-04-07 ');

->7

4.DAYOFYEAR (date)

Return date is the day of the year (within the range of 1 to 366)

SELECT dayofyear (' 2015-04-07 ');

->97

5.MONTH (date)

Returns the month value in date

SELECT MONTH (' 2015-04-07 ');

->4

6.DAYNAME (date)

Returns the day of the week (returned by English name)

Select Dayname ("2015-04-07");

->tuesday

7.MONTHNAME (date)

Returns a date that is a few months (returned by English name)

Select MONTHNAME ("2015-04-07");

->april

8.QUARTER (date)

Returns the first quarter of a year for date

SELECT QUARTER (' 2015-04-07 ');

->2

9.WEEK (Date,first)

Return date is the week ordinal of the year (first default 0,first value of 1 indicates that Monday is the beginning of the week, 0 starting from Sunday)

SELECT WEEK (' 2015-04-07 ');

->14

SELECT WEEK (' 2015-04-07 ', 0);

->14

SELECT WEEK (' 2015-04-07 ', 1);

->15

10.YEAR (date)

Returns the date of the year (in the range 1000 to 9999)

SELECT year (' 2015-04-07 ');

->2015

11.HOUR (Time)

Number of hours to return time (range 0 to 23)

Select HOUR (' 10:05:03 ');

->10

12.MINUTE (Time)

Number of minutes to return time (range 0 to 59)

SELECT MINUTE (' 2015-04-07 10:05:03 ');

->5

13.SECOND (Time)

Returns the number of seconds in time (range 0 to 59)

SELECT SECOND (' 10:05:03 ');

->3

14.period_add (P,n)

Add n months to a period p and return (P's format yymm or YYYYMM)

SELECT Period_add (201504,2);

->201506

15.period_diff (P1,P2)

Returns the number of months between the period P1 and P2 (format yymm or yyyymm of P1 and P2)

SELECT Period_diff (9802,199703);

->11

16.date_add (Date,interval expr type), date_sub (Date,interval expr type), adddate (Date,interval expr type), Subdate (DATE , INTERVAL expr type)

Adding and subtracting date time

Adddate () and subdate () are synonyms of date_add () and date_sub (), or they can be used with operators + and-instead of functions

Date is a datetime or date value in which expr adds and subtracts a date by an expression string type that indicates how the expression expr should be interpreted

[Type value meaning expected expr format]:

SECOND sec SECONDS
MINUTE min MINUTES
HOUR Time HOURS
Day days
Month MONTHS
Year years
Minute_second minutes and seconds "Minutes:seconds"
Hour_minute hours and minutes "hours:minutes"
Day_hour Day and Hour "days HOURS"
Year_month year and month "Years-months"
Hour_second hours, minutes, "HOURS:MINUTES:SECONDS"
Day_minute day, hour, minute "Days Hours:minutes"
Day_second day, hour, minute, second "days HOURS:MINUTES:SECONDS"

Any punctuation in expr is allowed to be delimited, and if all is a date value the result is a date value, otherwise the result is a datetime value)

If the type keyword is incomplete, then MySQL takes the value from the right end, day_second because the missing hour minute equals Minute_second)

If you increase month, year_month, or year, the maximum number of days is the maximum number of days greater than the result month)

Select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;

->1998-01-01 00:00:00

Select INTERVAL 1 day + "1997-12-31";

->1998-01-01

Select "1998-01-01"-INTERVAL 1 SECOND;

->1997-12-31 23:59:59

　　Select Date_add ("1997-12-31 23:59:59", INTERVAL 1 SECOND);

->1998-01-01 00:00:00

　　Select Date_add ("1997-12-31 23:59:59", INTERVAL 1 day );

->1998-01-01 23:59:59

　　Select Date_add ("1997-12-31 23:59:59", INTERVAL "1:1" minute_second);

->1998-01-01 00:01:00

Select Date_sub ("1998-01-01 00:00:00", INTERVAL "1:1:1:1" Day_second);

->1997-12-30 22:58:59

Select Date_add ("1998-01-01 00:00:00", INTERVAL " -1:10" day_hour);

->1997-12-30 14:00:00

Select Date_sub ("1998-01-02", INTERVAL Day);

->1997-12-02

Select EXTRACT (Year from "1999-07-02");

->1999

SELECT EXTRACT (year_month from "1999-07-02 01:02:03");

->199907

SELECT EXTRACT (Day_minute from "1999-07-02 01:02:03");

->20102

17.to_days (date)

Return date dates is 00 to present how many days (not calculated before 1582)

Select To_days (150407);

->736060

SELECT to_days (' 2015-04-07 ');

->736060

18.from_days (N)

Given the number of days 00 to date returns the date value (not calculated until 1582)

SELECT from_days (736060);

->2015-04-07

19.date_format (Date,format)

Formatting a date value based on the format string

Available markers in format string:
%M month name (January ... December)
%W Week name (Sunday ... Saturday)
%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). ）
%Y year, number, 4 bit
%y year, number, 2 bit
%a abbreviated weekday name (Sun ... Sat)
Number of days in the month of%d, number (00 ...). 31)
Number of days in%e month, number (0 ... 31)
%m Month, number (01 ... 12)
%c month, number (1 ... 12)
%b Abbreviated month name (Jan ... DEC)
%j Days of the year (001 ... 366)
%H hours (00 ... 23)
%k hours (0 ... 23)
%h hours (01 ... 12)
%I Hours (01 ... 12)
%l hours (1 ... 12)
%i minutes, Numbers (00 ... 59)
%r time, 12 hours (Hh:mm:ss [ap]m)
%T time, 24 hours (HH:MM:SS)
%s seconds (00 ... 59)
%s seconds (00 ... 59)
%p am or PM
%w days in one weeks (0=sunday ... 6=saturday)
%u Week (0 ... 52), here Sunday is the first day of the week
%u Week (0 ... 52), here Monday is the first day of the week
Percent character%

　　

SELECT date_format (' 1997-10-04 22:23:00 ', '%W%M%Y ');

->saturday October 1997

Select Date_format (' 1997-10-04 22:23:00 ', '%h:%i:%s ');

->22:23:00

SELECT date_format (' 1997-10-04 22:23:00 ', '%d%y%a%d%m%b%j ');

->4th Sat OCT 277

SELECT date_format (' 1997-10-04 22:23:00 ', '%H%k%I%r%T%s%w ');

->22 10:23:00 PM 22:23:00 00 6

20.time_format (Time,format)

Similar to Date_format (), but Time_format only handles hours, minutes, and seconds (the remaining symbols produce a null value or 0)

SELECT Time_format (' 1997-10-04 22:23:00 ', '%H ');

->22

21.CURDATE (), Current_date ()

Returns the current date value in ' Yyyy-mm-dd ' or YYYYMMDD format (a string or number based on the context in which the return value is located)

SELECT curdate ();

->2015-04-08

SELECT curdate () + 0;

->20150408

22.CURTIME (), Current_time ()

Returns the current time value in ' HH:MM:SS ' or HHMMSS format (a string or number based on the context in which the return value is located)

SELECT Curtime ();

->09:03:48

Select Curtime () + 0;

->90425

23.NOW (), Sysdate (), Current_timestamp ()

Returns the current datetime in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format (a string or number based on the context in which the return value is located)

Select Now ();

->2015-04-08 09:07:48

Select now () + 0;

->20150408091501

24.unix_timestamp (), Unix_timestamp (date)

Returns a UNIX timestamp (the number of seconds starting from ' 1970-01-01 00:00:00 ' GMT, the date default is the current time)

Select Unix_timestamp ();

->1428455750

Select Unix_timestamp (' 1997-10-04 22:23:00 ');

->875974980

25.from_unixtime (Unix_timestamp)

Returns the timestamp value in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format (a string or number based on the context in which the return value is located)

SELECT From_unixtime (875996580);

->1997-10-05 04:23:00

SELECT From_unixtime (875996580) + 0;

->19971005042300

26.from_unixtime (Unix_timestamp,format)

Returns the timestamp value as format string

SELECT From_unixtime (Unix_timestamp (), '%Y%d%M%h:%i:%s%x ');

->2015 8th April 09:21:22 2015

27.sec_to_time (seconds)

A time value that returns the number of seconds in ' HH:MM:SS ' or HHMMSS format (a string or number based on the context in which the return value is located)

SELECT Sec_to_time (2378);

->00:39:38

Select Sec_to_time (2378) + 0;

->3938

28.time_to_sec (Time)

Returns how many seconds the time value is

SELECT time_to_sec (' 22:23:00 ');

->80580

MySQL functions Daquan