YouTip

MySQL has many built-in functions. The following lists the descriptions of these functions. * * * ## MySQL String Functions | Function | Description | Example | | --- | --- | --- | | ASCII(s) | Returns the ASCII code value of the first character of string s. | Returns the ASCII code of the first letter of the CustomerName field: SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers; | | CHAR_LENGTH(s) | Returns the number of characters in string s. | Returns the number of characters in the string "": SELECT CHAR_LENGTH("") AS LengthOfString; | | CHARACTER_LENGTH(s) | Returns the number of characters in string s, equivalent to CHAR_LENGTH(s). | Returns the number of characters in the string "": SELECT CHARACTER_LENGTH("") AS LengthOfString; | | CONCAT(s1,s2...sn) | Concatenates multiple strings s1, s2, etc., into a single string. | Concatenates multiple strings: SELECT CONCAT("SQL ", " ", "Gooogle ", "Facebook") AS ConcatenatedString; | | CONCAT_WS(x, s1,s2...sn) | Same as CONCAT(s1,s2,...) function, but adds x between each string. x can be a separator. | Concatenates multiple strings with a separator: SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString; | | FIELD(s,s1,s2...) | Returns the position of the first string s in the string list (s1,s2...). | Returns the position of string "c" in the list values: SELECT FIELD("c", "a", "b", "c", "d", "e"); | | FIND_IN_SET(s1,s2) | Returns the position of the string matching s1 in the string s2. | Returns the position of string "c" in the specified string: SELECT FIND_IN_SET("c", "a,b,c,d,e"); | | FORMAT(x,n) | Formats the number x as "#,###.##", rounding x to n decimal places. | Formats a number in "#,###.##" form: SELECT FORMAT(250500.5634, 2); -- Outputs 250,500.56 | | INSERT(s1,x,len,s2) | Replaces the substring in s1 starting at position x with length len with string s2. | Replaces the first 6 characters of the string with "": SELECT INSERT("google.com", 1, 6, ""); -- Outputs: | | LOCATE(s1,s) | Gets the starting position of s1 in string s. | Gets the position of 'st' in 'myteststring': SELECT LOCATE('st','myteststring'); -- 5. Returns the position of 'b' in 'abc': SELECT LOCATE('b', 'abc') -- 2 | | LCASE(s) | Converts all letters in string s to lowercase. | Converts string "" to lowercase: SELECT LCASE('') -- | | LEFT(s,n) | Returns the first n characters of string s. | Returns the first two characters of the string "": SELECT LEFT('',2) -- ru | | LOWER(s) | Converts all letters in string s to lowercase. | Converts string "" to lowercase: SELECT LOWER('') -- | | LPAD(s1,len,s2) | Pads the beginning of string s1 with string s2 until the length reaches len. | Pads the beginning of "abc" with "xx": SELECT LPAD('abc',5,'xx') -- xxabc | | LTRIM(s) | Removes leading spaces from string s. | Removes leading spaces from " ": SELECT LTRIM(" ") AS LeftTrimmedString;-- | | MID(s,n,len) | Extracts a substring from string s starting at position n with length len, equivalent to SUBSTRING(s,n,len). | Extracts 3 characters starting from the 2nd position in "": SELECT MID("", 2, 3) AS ExtractString; -- UNO | | POSITION(s1 IN s) | Gets the starting position of s1 in string s. | Returns the position of 'b' in 'abc': SELECT POSITION('b' in 'abc') -- 2 | | REPEAT(s,n) | Repeats string s n times. | Repeats "" three times: SELECT REPEAT('',3) -- tutorialtutorialtutorial | | REPLACE(s,s1,s2) | Replaces all occurrences of s1 in string s with s2. | Replaces 'a' with 'x' in "abc": SELECT REPLACE('abc','a','x') --xbc | | REVERSE(s) | Reverses the order of characters in string s. | Reverses the string "abc": SELECT REVERSE('abc') -- cba | | RIGHT(s,n) | Returns the last n characters of string s. | Returns the last two characters of "": SELECT RIGHT('',2) -- ob | | RPAD(s1,len,s2) | Pads the end of string s1 with string s2 until the length reaches len. | Pads the end of "abc" with "xx": SELECT RPAD('abc',5,'xx') -- abcxx | | RTRIM(s) | Removes trailing spaces from string s. | Removes trailing spaces from " ": SELECT RTRIM(" ") AS RightTrimmedString; -- | | SPACE(n) | Returns n spaces. | Returns 10 spaces: SELECT SPACE(10); | | STRCMP(s1,s2) | Compares strings s1 and s2. Returns 0 if s1 = s2, 1 if s1 > s2, and -1 if s1 2017-06-25 | | ADDTIME(t,n) | Adds a time expression n to time t. | Adds 5 seconds: SELECT ADDTIME('2011-11-11 11:11:11', 5);->2011-11-11 11:11:16 (seconds). Adds 2 hours, 10 minutes, 5 seconds: SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26 | | CURDATE() | Returns the current date. | SELECT CURDATE();-> 2018-09-19 | | CURRENT_DATE() | Returns the current date. | SELECT CURRENT_DATE();-> 2018-09-19 | | CURRENT_TIME | Returns the current time. | SELECT CURRENT_TIME();-> 19:59:02 | | CURRENT_TIMESTAMP() | Returns the current date and time. | SELECT CURRENT_TIMESTAMP()-> 2018-09-19 20:57:43 | | CURTIME() | Returns the current time. | SELECT CURTIME();-> 19:59:02 | | DATE() | Extracts the date value from a date or datetime expression. | SELECT DATE("2017-06-15"); -> 2017-06-15 | | DATEDIFF(d1,d2) | Calculates the number of days between dates d1 and d2. | SELECT DATEDIFF('2001-01-01','2001-02-02')-> -32 | | DATE_ADD(d, INTERVAL expr type) | Calculates the date after adding a time interval to the start date d. The type value can be: * MICROSECOND * SECOND * MINUTE * HOUR * DAY * WEEK * MONTH * QUARTER * YEAR * SECOND_MICROSECOND * MINUTE_MICROSECOND * MINUTE_SECOND * HOUR_MICROSECOND * HOUR_SECOND * HOUR_MINUTE * DAY_MICROSECOND * DAY_SECOND * DAY_MINUTE * DAY_HOUR * YEAR_MONTH | SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); -> 2017-06-25. SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);-> 2017-06-15 09:49:21. SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);->2017-06-15 06:34:21. SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 MONTH);->2017-03-15 09:34:21 | | DATE_FORMAT(d,f) | Displays date d according to the format specified by expression f. | SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')-> 2011-11-11 11:11:11 AM | | DATE_SUB(date, INTERVAL expr type) | Subtracts a specified time interval from a date. | Subtracts 2 days from the OrderDate field in the Orders table: SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders | | DAY(d) | Returns the day part of date d. | SELECT DAY("2017-06-15"); -> 15 | | DAYNAME(d) | Returns the name of the weekday for date d, such as Monday, Tuesday. | SELECT DAYNAME('2011-11-11 11:11:11')->Friday | | DAYOFMONTH(d) | Calculates the day of the month for date d. | SELECT DAYOFMONTH('2011-11-11 11:11:11')->11 | | DAYOFWEEK(d) | Returns the weekday number for date d, where 1 is Sunday, 2 is Monday, and so on. | SELECT DAYOFWEEK('2011-11-11 11:11:11')->6 | | DAYOFYEAR(d) | Calculates the day of the year for date d. | SELECT DAYOFYEAR('2011-11-11 11:11:11')->315 | | EXTRACT(type FROM d) | Extracts a specific value from date d. The type specifies the value to return. The type can be: * MICROSECOND * SECOND * MINUTE * HOUR * DAY * WEEK * MONTH * QUARTER * YEAR * SECOND_MICROSECOND * MINUTE_MICROSECOND * MINUTE_SECOND * HOUR_MICROSECOND * HOUR_SECOND * HOUR_MINUTE * DAY_MICROSECOND * DAY_SECOND * DAY_MINUTE * DAY_HOUR * YEAR_MONTH | SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') -> 11 | | FROM_DAYS(n) | Calculates the date n days after 0000-01-01. | SELECT FROM_DAYS(1111)-> 0003-01-16 | | HOUR(t) | Returns the hour value from t. | SELECT HOUR('1:2:3')-> 1 | | LAST_DAY(d) | Returns the last day of the month for the given date. | SELECT LAST_DAY("2017-06-20");-> 2017-06-30 | | LOCALTIME() | Returns the current date and time. | SELECT LOCALTIME()-> 2018-09-19 20:57:43 | | LOCALTIMESTAMP() | Returns the current date and time. | SELECT LOCALTIMESTAMP()-> 2018-09-19 20:57:43 | | MAKEDATE(year, day-of-year) | Returns a date based on the given year and day-of-year. | SELECT MAKEDATE(2017, 3);-> 2017-01-03 | | MAKETIME(hour, minute, second) | Combines time components: hour, minute, second. | SELECT MAKETIME(11, 35, 4);-> 11:35:04 | | MICROSECOND(date) | Returns the microsecond part of the date parameter. | SELECT MICROSECOND("2017-06-20 09:34:00.000023");-> 23 | | MINUTE(t) | Returns the minute value from t. | SELECT MINUTE('1:2:3')-> 2 | | MONTHNAME(d) | Returns the name of the month for date d, such as November. | SELECT MONTHNAME('2011-11-11 11:11:11')-> November | | MONTH(d) | Returns the month value from date d, from 1 to 12. | SELECT MONTH('2011-11-11 11:11:11')->11 | | NOW() | Returns the current date and time. | SELECT NOW()-> 2018-09-19 20:57:43 | | PERIOD_ADD(period, number) | Adds a number of months to a year-month period. | SELECT PERIOD_ADD(201703, 5); -> 201708 | | PERIOD_DIFF(period1, period2) | Returns the difference in months between two periods. | SELECT PERIOD_DIFF(201710, 201703);-> 7 | | QUARTER(d) | Returns the quarter of the year for date d, from 1 to 4. | SELECT QUARTER('2011-11-11 11:11:11')-> 4 | | SECOND(t) | Returns the second value from t. | SELECT SECOND('1:2:3')-> 3 | | SEC_TO_TIME(s) | Converts time in seconds to a time format (hours:minutes:seconds). | SELECT SEC_TO_TIME(4320)-> 01:12:00 | | STR_TO_DATE(string, format_mask) | Converts a string to a date. | SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");-> 2017-08-10 | | SUBDATE(d,n) | Subtracts n days from date d. | SELECT SUBDATE('2011-11-11 11:11:11', 1)->2011-11-10 11:11:11 (default is days) | | SUBTIME(t,n) | Subtracts n seconds from time t. | SELECT SUBTIME('2011-11-11 11:11:11', 5)->2011-11-11 11:11:06 (seconds) | | SYSDATE() | Returns the current date and time. | SELECT SYSDATE()-> 2018-09-19 20:57:43 | | TIME(expression) | Extracts the time part from the given expression. | SELECT TIME("19:30:10");-> 19:30:10 | | TIME_FORMAT(t,f) | Displays time t according to the format specified by expression f. | SELECT TIME_FORMAT('11:11:11','%r')11:11:11 AM | | TIME_TO_SEC(t) | Converts time t to seconds. | SELECT TIME_TO_SEC('1:12:00')-> 4320 | | TIMEDIFF(time1, time2) | Calculates the time difference between two times. | mysql> SELECT TIMEDIFF("13:10:11", "13:10:10");-> 00:00:01. mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', -> '2000:01:01 00:00:00.000001'); -> '-00:00:00.000001'. mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001', -> '2008-12-30 01:01:01.000002'); -> '46:58:57.999999' | | TIMESTAMP(expression, interval) | With a single parameter, returns a date or datetime expression. With two parameters, adds the interval to the first parameter. | mysql