Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the string functions of MySQL database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what the MySQL database string functions are.

The first category: string function

1. Conv: convert the number n of from_base to the representation of to_base (PS: the range of base is 2-36, when to_base is negative, n is signed or unsigned)

Mysql > select conv ("a", 16pm 2)

-> '1010'

Mysql > select conv ("6e", 16penny 10)

-> '110'

Mysql > select conv (- 17pr 16mai Murray 10)

->'- 23'

2. Bin (n) / oct (n) / hex (n): returns the binary / octal / hexadecimal representation of the decimal number n

Mysql > select bin (12)

-> '1100'

Mysql > select oct (12)

-> '14'

Mysql > select hex (255)

-> 'ff'

3. Char (n...): returns a string composed of characters corresponding to multiple ascii codes.

Mysql > select char (77, 121, 83, 81, 76)

-> 'mysql'

Mysql > select char (77, 7. 3, 7. 3, 7. 3)

-> 'mmm'

4. Concat (str1,str2,...): concatenates multiple parameters into a string, and returns null as long as one of them is null.

Mysql > select concat ('my', 's shoes,' ql')

-> 'mysql'

Mysql > select concat ('my', null,' ql')

-> null

Mysql > select concat (14.3)

-> '14.3'

4. Length (str) / octet_length (str): returns all bytes of the default character set corresponding to the parameter.

5. Char_length (str) / character_length (str): returns the character length of the string str

Mysql > select length ('text')

-> 4

Mysql > select octet_length ('text')

-> 4

6. Locate (substr,str) / position (substr instr) / instr (str,substr): returns the position of the string substr at the first occurrence of the string str (0 if str does not contain substr)

Mysql > select locate ('bar',' foobarbar')

-> 4

Mysql > select locate ('xbar',' foobar')

-> 0

Mysql > select instr ('foobarbar',' bar')

-> 4

7. Locate (substr,str,pos): returns the position of the string substr that first appears from the pos position of the string str (0 if str does not contain substr)

Mysql > select locate ('bar',' foobarbar',5)

-> 7

8. Lpad (str,len,padstr) / rpad (str,len,padstr): fill the left / right end of str with the string padstr until the string length is len and return

Mysql > select lpad ('hi',4,'??')

->'?? hi'

Mysql > select rpad ('hi',5,'?')

-> 'hi???'

9. Left (str,len) / right (str,len): returns the len characters at the left / right end of the string str

Mysql > select left ('foobarbar', 5)

-> 'fooba'

Mysql > select right ('foobarbar', 4)

-> 'rbar'

10. Substring (str,pos,len) / substring (str from pos for len) / mid (str,pos,len): returns the position of the string str pos from len characters

Mysql > select substring ('quadratically',5,6)

-> 'ratica'

11. Substring (str,pos) / substring (str from pos): returns a substring from the position of the string str pos. If pos is negative, the reciprocal is truncated to the end.

Mysql > select substring ('quadratically',5)

-> 'ratically'

Mysql > select substring ('foobarbar' from 4)

-> 'barbar'

12. Substring_index (str,delim,count): returns the substring after the delimiter delim that appears at the count of the string str

(return the left end if count is positive, otherwise return the right terminal string)

Mysql > select substring_index ('MySQL','. 2)

-> 'www.mysql'

Mysql > select substring_index ('www.mysql.com','.'- 2)

-> 'mysql.com'

13. Ltrim (str) / rtrim (str): returns the string str with left / right spaces deleted

Mysql > select ltrim ('barbar')

-> 'barbar'

Mysql > select rtrim ('barbar')

-> 'barbar'

14. Trim ([[both | leading | trailing] [remstr] from] str): returns the string str whose prefix or suffix remstr has been deleted (position parameter default both, remstrdefault is blank) 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'

15. Space (n): returns a string of n space characters

Mysql > select space (6)

->''

16. Replace (str,from_str,to_str): replace the substring from_str in the string str with the string to_str and return

Mysql > select replace ('www.mysql.com',' walled, 'ww')

-> 'wwwwww.mysql.com'

17. Repeat (str,count): returns a string concatenated by count strings str (when any parameter is null

Returns null,count select repeat ('mysql', 3)

-> 'mysqlmysqlmysql'

18. Reverse (str): reverses the character order of the string str and returns

Mysql > select reverse ('abc')

-> 'cba'

19. Insert (str,pos,len,newstr)

Function: replace the string str from the position pos with the substring of len characters long with the string newstr and return

Mysql > select insert ('quadratic', 3,4,' what')

-> 'quwhattic'

20. Lcase (str) / lower (str) / ucase (str) / upper (str): returns the case of the str

21. Elt: returns the nth string (n less than 1 or greater than the number of parameters return null)

Mysql > select elt (1, 'ej',' heja', 'hej',' foo')

-> 'ej'

Mysql > select elt (4, 'ej',' heja', 'hej',' foo')

-> 'foo'

2. Mathematical function

1. Pow (x ~ ()) / power (x ~ ()): returns the value x to the power of y

Mysql > select pow (2jue 2)

-> 4.000000

Mysql > select pow (2Mae Murray 2)

-> 0.250000

2. Sqrt (n): returns the square root of the non-negative number n

Mysql > select sqrt (4)

-> 2.000000

Mysql > select sqrt (20)

-> 4.472136

3. Pi (): returns pi

Mysql > select pi ()

-> 3.141593

4. Rand () / rand (n): returns a random floating-point value in the range 0 to 1.0 (you can use the number n as the initial value)

Mysql > select rand ()

-> 0.5925

Mysql > select rand (20)

-> 0.1811

Mysql > select rand (20)

-> 0.1811

Mysql > select rand ()

-> 0.2079

Mysql > select rand ()

-> 0.7888

5. Truncate (n): keep the d decimal place of the number n and return it

Mysql > select truncate (1.223pc1)

-> 1.2

Mysql > select truncate (1.999jue 1)

-> 1.9

Mysql > select truncate (1.999)

-> 1

6. Least: returns the minimum value (if the return values are used in the context of integers (real or size-sensitive strings) or if all parameters are integers (real or size-sensitive strings), they are compared as integers (real or size-sensitive strings), otherwise they are compared as strings that ignore case.

Mysql > select least (2J0)

-> 0

Mysql > select least (34.0, 3.0pm, 5.0767.0)

-> 3.0

Mysql > select least ("b", "a", "c")

-> "a"

7. Greatest: return the maximum value (the rest is the same as least ()).

Mysql > select greatest (2J0)

-> 2

Mysql > select greatest (34.0, 3.0pm, 5.0767.0)

-> 767.0

Mysql > select greatest ("b", "a", "c")

-> "c"

8. Mod (m): take the module operation and return the remainder of n divided by m (same as% operator)

9. Floor (n): returns the maximum integer value not greater than n

10. Ceiling (n): returns the minimum integer value of not less than n

Mysql > select ceiling (1.23)

-> 2

Mysql > select ceiling (- 1.23)

->-1

11. Round (n): returns the rounded value of n and retains d decimal places (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

12. Abs (n): returns the absolute value of n

13. Sign (n): returns the symbol of the parameter (is-1, 0, or 1)

Mysql > select sign (- 32)

->-1

Mysql > select sign (0)

-> 0

Mysql > select sign

-> 1

14. Exp (n): returns the nth power of the value e (the base of the natural logarithm)

Mysql > select exp (2)

-> 7.389056

Mysql > select exp (- 2)

-> 0.135335

15. Log (n): returns the natural logarithm of n

Mysql > select log (2)

-> 0.693147

Mysql > select log (- 2)

-> null

16. Log10 (n): returns the logarithm of n with base 10

Mysql > select log10 (2)

-> 0.301030

Mysql > select log10

-> 2.000000

Mysql > select log10 (- 100)

-> null

17. Cos (n): returns the cosine of n

Mysql > select cos (pi ())

->-1.000000

18. Sin (n): returns the sine of n

Mysql > select sin (pi ())

-> 0.000000

19. Tan (n): returns the tangent of n

Mysql > select tan (pi () + 1)

-> 1.557408

20. Acos (n): returns n inverse cosine (n is cosine in the range of-1 to 1, otherwise null)

Mysql > select acos (1)

-> 0.000000

Mysql > select acos (1.0001)

-> null

Mysql > select acos (0)

-> 1.570796

21. Asin (n): returns n arcsine

Mysql > select asin (0.2)

-> 0.201358

Mysql > select asin ('foo')

-> 0.000000

22. Atan (n): returns the arc tangent of n

Mysql > select atan (2)

-> 1.107149

Mysql > select atan (- 2)

->-1.107149

23. Atan2 (XQuery y): returns the arc tangent of 2 variables x and y (similar to the anti-tangent of YUnix, the symbol determines the quadrant)

Mysql > select atan (- 2)

->-0.785398

Mysql > select atan (pi (), 0)

-> 1.570796

24. Cot (n): returns the cotangent of x

Mysql > select cot (12)

->-1.57267341

Mysql > select cot (0)

-> null

25. Degrees (n): converts n from radians to angles and returns

Mysql > select degrees (pi ())

-> 180.000000

26. Radians (n): converts n from angle to Radian and returns

Mysql > select radians (90)

-> 1.570796

III. Time function of period

1. Curdate () / current_date (): returns the current date value in 'yyyy-mm-dd' or yyyymmdd format (a string or number depending on the context in which the value is returned)

Mysql > select curdate ()

-> '1997-12-15'

Mysql > select curdate () + 0

-> 19971215

2. Curtime () / current_time (): returns the current time value in 'hh:mm:ss' or hhmmss format (depending on the context of the returned value is a string or number)

Mysql > select curtime ()

->'23 50 14 26'

Mysql > select curtime () + 0

-> 235026

3. Now () / sysdate () / current_timestamp (): returns the current date in 'yyyy-mm-dd hh:mm:ss' or yyyymmddhhmmss format

Time (depending on the context in which the value is returned is a string or number)

Mysql > select now ()

-> '1997-12-15 23 23 50 purl 26'

Mysql > select now () + 0

-> 19971215235026

4. Dayofweek (date): what day of the week is the return date date (1 = Sunday, 2 = Monday, … 7 = Saturday, odbc standard)

Mysql > select dayofweek ('1998-02-03')

-> 3

5. Weekday (date): return date date is what day of the week (0 = Monday, 1 = Tuesday, …... 6 = Sunday).

Mysql > select weekday ('1997-10-04 22 purge 23purl 00')

-> 5

Mysql > select weekday ('1997-11-05')

-> 2

6. Dayname (date): returns the day of the week date (returned by English name)

Mysql > select dayname ("1998-02-05")

-> 'thursday'

7. Dayofmonth (date): returns the day of the month on which the date is (in the range of 1 to 31)

Mysql > select dayofmonth ('1998-02-03')

-> 3

8. Dayofyear (date): returns the day of the year on which date is returned (in the range of 1 to 366)

Mysql > select dayofyear ('1998-02-03')

-> 34

9. Month (date): returns the month value in date

Mysql > select month ('1998-02-03')

-> 2

10. Monthname (date): the number of months in which date is returned (returned by English name)

Mysql > select monthname ("1998-02-05")

-> 'february'

11. Quarter (date): returns the quarter of the year in which date is returned

Mysql > select quarter ('98-04-01')

-> 2

12. Week (date,first): returns the week of the year when date is returned. (the first default value of 0 first indicates that Monday is the beginning of the week, and 0 starts on Sunday.)

Mysql > select week ('1998-02-20')

-> 7

Mysql > select week ('1998-02-20)

-> 7

Mysql > select week ('1998-02-20)

-> 8

13. Year (date): returns the year of the date (range from 1000 to 9999)

Mysql > select year ('98-02-03')

-> 1998

14. Hour (time): returns the number of hours of time (range 0 to 23)

Mysql > select hour ('10 05purl 03')

-> 10

15. Minute (time): returns the number of minutes of time (range: 0 to 59)

Mysql > select minute ('98-02-03 10 purl 05purl 03')

-> 5

16. Second (time): the number of seconds to return time (range 0 to 59)

Mysql > select second ('10 05purl 03')

-> 3

17. Period_add (pQuery n): add n months to period p and return (format yymm or yyyymm of p)

Mysql > select period_add (9801 Magi 2)

-> 199803

18. Period_diff (p1 and p2): returns the number of months between periods p1 and p2 (p1 and p2 format yymm or yyyymm)

Mysql > select period_diff (9802 ~ 199703)

-> 11

19. Date_add (date,interval expr type) / date_sub (date,interval expr type) / adddate (date,interval expr type) / subdate (date,interval expr type) adds and subtracts the date and time. (you can also use the operators + and -. Date: a datetime or date value; expr: an expression string that adds or subtracts date; type indicates how the expression expr should be interpreted)

The format of type meaning expression

Second second seconds

Minute score minutes

Hours during hour

Day days days

Month monthly months

Year year years

Minute_second minutes and seconds minutes:seconds

Hour_minute hour and minute hours:minutes

Day_hour days and hours days:hours

Year_month year and month years-months

Hour_second hours minutes seconds hours:minutes:seconds

Day_minute day hour minute days:hours:minutes

Day_second days, hours, minutes, seconds days:hours:minutes:seconds

Any punctuation is allowed as a delimiter in expr. If all are date values, the result is a date value, otherwise the result is a datetime value. Day_second equals minute_second because of lack of hours and minutes. If you increase month, year_month, or year, the maximum number of days is used if the number of days is greater than the maximum number of days of the result month.

Mysql > select "1997-12-31 23:59:59" + interval 1 second

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

Mysql > select interval 1 day + "1997-12-31"

-> 1998-01-01

Mysql > select "1998-01-01"-interval 1 second

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

Mysql > select date_add ("1997-12-31 23:59:59", interval 1 second)

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

Mysql > select date_add ("1997-12-31 23:59:59", interval 1 day)

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

Mysql > select date_add ("1997-12-31 23:59:59", interval "1:1" minute_second)

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

Mysql > select date_sub ("1998-01-01 00:00:00", "interval" 1 day_second)

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

Mysql > select date_add ("1998-01-01 00:00:00", interval "- 1:10" day_hour)

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

Mysql > select date_sub (1998-01-02, interval 31 day)

-> 1997-12-02

20. Extract (part from date): used to intercept time components

Mysql > select extract (year from "1999-07-02")

-> 1999

Mysql > select extract (year_month from "1999-07-02 01:02:03")

-> 199907

Mysql > select extract (day_minute from "1999-07-02 01:02:03")

-> 20102

20. Date_format (date,format): format the date value according to the format string

(markers are available in format strings:

% m month name (january... December)

% w week name (sunday... Saturday)

D the date of the month with the English prefix (1st, 2nd, 3rd, etc.). )

% y year, number, 4 digits

% y year, number, 2 digits

% an abbreviated name of the week (sun... Sat)

Number of days in% d month, 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, number (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 a week (0=sunday... 6=saturday)

% U week (0 …... Here Sunday is the first day of the week

% u weeks (0. Here Monday is the first day of the week

% character%)

Mysql > select date_format ('1997-10-04 22 2300% m% y')

-> 'saturday october 1997'

Mysql > select date_format ('1997-10-04 22 2300% hrig% iRO% s')

->'22 2312 00'

Mysql > select date_format ('1997-10-04 22 2300 handwriting%% y% a% d% m% b% j')

-> '4th 97 sat 04 10 oct 277'

Mysql > select date_format ('1997-10-04 22 2300% k% I% r% t% s% w')

->'22 22 10 10:23:00 pm 22:23:00 00 6'

21. Time_format (time,format): similar to date_format (), but time_format only handles hours, minutes, and seconds (the rest of the symbols produce a null value or 0)

22. Unix_timestamp () | unix_timestamp (date): returns a unix timestamp (the number of seconds from '1970-01-01 00:00:00'gmt, which defaults to the current time)

Mysql > select unix_timestamp ()

-> 882226357

Mysql > select unix_timestamp ('1997-10-04 22 purge 23purl 00')

-> 875996580

23. From_unixtime (unix_timestamp): returns the timestamp value in 'yyyy-mm-dd hh:mm:ss' or yyyymmddhhmmss format (a string or number depending on the context in which the value is returned)

Mysql > select from_unixtime (875996580)

-> '1997-10-04 22 22 purl 2300'

Mysql > select from_unixtime (875996580) + 0

-> 19971004222300

24. From_unixtime (unix_timestamp,format): returns the value of the timestamp in format string format

Mysql > select from_unixtime (unix_timestamp (),'y d m h:%i:%s x')

-> '1997 23rd december 03:43:30 x'

25. Sec_to_time (seconds): returns the time value converted by the number of seconds in 'hh:mm:ss' or hhmmss format (depending on the context in which the value is returned).

Mysql > select sec_to_time (2378)

-> '00Rose 39RU 38'

Mysql > select sec_to_time (2378) + 0

-> 3938

26. Time_to_sec (time): returns how many seconds the time value is

Mysql > select time_to_sec ('22 2300')

-> 80580

Mysql > select time_to_sec ('00GRV 39RV 38')

-> 2378

At this point, I believe that you have a deeper understanding of "MySQL database string functions", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.