Common functions of mysql and SQL Server are organized to meet the urgent needs of MySQL.

Source: Internet
Author: User
Tags decimal to binary float number month name
Sort out common functions of mysql and SQL Server for emergency purposes

I haven't used database functions for a long time. I have used them today. By the way, I have sorted out the previous ones. I personally think it is almost the same. I only have three databases: mysql, sqlserver, and oracle. since oracle is too memory-consuming, it is not useful in the company, let's sort out mysql and sqlserver today.

MySQL

Character class:

CHARSET (str) // returns the string character set CONCAT (string2 [,...]) // connection string INSTR (string, substring) // returns the position of the first occurrence of the substring in the string. If no position exists, 0 LCASE (string2) is returned. // converts the value to lower-case LEFT (string2, length) // take the length (string) character from the left of string2 // string LENGTH LOAD_FILE (file_name) // read the content from the file LOCATE (substring, string [, start_position]) same as INSTR, but you can specify the start position LPAD (string2, length, pad) // repeat pad at the start of string until the string length is lengthLTRIM (string2) // remove front-end space REPEAT (string2, count) // REPEAT count REPLACE (str, search_str, replace_str) // REPLACE search_strRPAD (string2, length, pad) with replace_str in str) // use pad after str until the length is lengthRTRIM (string2) // remove backend spaces STRCMP (string1, string2) // compare the size of two strings by character, SUBSTRING (str, position [, length]) // starts from the position of str and takes length characters. note: when processing strings in mysql, the first character subscript is 1 by default, that is, the position parameter must be greater than or equal to 1.

Mathematics:

ABS (number2) // absolute value BIN (decimal_number) // decimal to binary CEILING (number2) // rounded up to CONV (number2, from_base, to_base) // Convert FLOOR (number2) in hexadecimal notation // round-down the entire FORMAT (number, decimal_places) // retain the number of decimal places HEX (DecimalNumber) // Convert to hexadecimal notation note: HEX () if a string can be passed in, its ASC-11 code is returned. for example, HEX ('def ') returns 4142143 or a decimal integer, returning its hexadecimal encoding, such as HEX (25) returns 19 LEAST (number, number2 [,...]). // Evaluate the minimum MOD (numerator, denominator) // Evaluate the remainder POWER (number, power) // Evaluate the exponent RAND ([seed]) // random number ROUND (number [, decimals]) // rounding, decimals is the number of decimal places]

Date type:


ADDTIME (date2, time_interval) // add time_interval to date2CONVERT_TZ (datetime2, fromTZ, toTZ) // Convert the time zone CURRENT_DATE () // Current date CURRENT_TIME () // Current Time CURRENT_TIMESTAMP () // Current timestamp DATE (datetime) // returns the DATE part of datetime DATE_ADD (date2, INTERVAL d_value d_type) // add the DATE or time DATE_FORMAT (datetime, FormatCodes) to date2) // Display datetimeDATE_SUB (date2, INTERVAL d_value d_type) in formatcodes format // subtract a time DATEDIFF (date1, date2) from date2 // Two date difference days (date) // return the day DAYNAME (date) of the date // English week DAYOFWEEK (date) // Week (1-7), 1 is Sunday DAYOFYEAR (date) // the day of the year EXTRACT (interval_name FROM date) // EXTRACT the specified part of the date FROM the date MAKEDATE (year, day) // give the day of the year and the year, generate date string MAKETIME (hour, minute, second) // Generate time string MONTHNAME (date) // English month name NOW () // Current Time SEC_TO_TIME (seconds) // Convert seconds to time STR_TO_DATE (string, format) // string to time, display TIMEDIFF (datetime1, datetime2) in format // Two time difference TIME_TO_SEC (time) // Time to seconds] WEEK (date_time [, start_of_week]) // week year (datetime) // year dayofmonth (datetime) // day of month HOUR (datetime) // hour LAST_DAY (date) // The last date of the MONTH of date MICROSECOND (datetime) // microsecond month (datetime) // month minute (datetime) // MINUTE return symbol, plus or minus 0 SQRT (number2) // Square



SQL Server


Character class

Select CHARINDEX ('ly ', 'My name is Ly') -- find the starting position of a character in a string select LEN ('zhangsan ') -- get the length of a string select UPPER ('Yang') -- convert a lowercase string to an uppercase select LTRIM ('zhangsan ') -- remove the space on the left of a character select RTRIM ('zhangsan') -- remove the space on the RIGHT of a character select RIGHT ('ly, gentleman Yao ', 4) -- returns the specified length from the right side of a string: select LEFT ('ly, Yao Zhi', 2) -- returns the specified length from the LEFT side of a string: select STUFF ('abcdefg ', 2, 4, 'Zhang San') -- delete a string of the specified length from the specified position and REPLACE it with the new string select REPLACE ('yangzi Zhiyao ', 'yangzi', 'Jun ') -- replace the specified string in a string with another string



Date functions

Select GETDATE () -- get the current system Time select DATEADD (MM, 1, '2017-12-2 ') -- Add the specified value to the specified date segment and then select DATEDIFF (mm, '2014-6-15 ', GETDATE () -- select DATENAME (DW, '2014-1-1') for the specified interval between two time periods ') -- select DATEPART (mm, '2017-01-01 ') for the string format of the specified time period in the specified date string -- get the integer form of the specified date part



Mathematical functions

Select RAND () -- obtain the random float number between 0 and 1 select ABS (-10) -- obtain the absolute value select CEILING (23.1) -- Take the integer form of a float value, select FLOOR (23.1) -- same as above, but this function is a downward integer select POWER (199.119) -- select ROUND (, 2) to the POWER of a value) -- returns the rounding form of a float value and takes the specified reserved decimal places select SIGN (-10) -- Returns-1 if it is a negative number, and returns 1 if it is a positive number, 0 returns 0 select SQRT (25) -- returns the square root of a number.



System functions

Select CONVERT (int, 12.6) -- converts a data to a specified data type select DATALENGTH ('ABCDE') -- returns the length of a string.





Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.