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

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.