SQL--functions

This chapter mainly talks about SQL common functions

One, the remainder function mod (x, y)

MoD (x, y) returns the remainder after x is removed by y, and the MoD () also works for numbers with fractional parts, which returns the exact remainder after the divisor.

Example

1. The divisor and divisor are all integers

2. Divisor or divisor with the same number of decimal places

3. The divisor and divisor have different decimal places, and the number of decimal places is the divisor.

Second, rounding function round (x, y) and intercept after decimal point n-bit function truncate (x, y)

TRUNCATE (x, y) returns the number x that is removed to the Y-digit after the decimal point, and if the value of y is 0, the result is not a fractional fraction.

1.0 digits after the decimal point

2.1 digits after the decimal point

ROUND (x, y) returns the number x,x to the Y-position after the decimal point is rounded, and if the value of y is 0, the result is not a fractional part, and the result is rounded.

1. Rounding without decimals, quad loft

2. Rounding without decimals, five in

3. Five-in with one decimal place

Note: 8.36 with a decimal rounding, 0.06>=0.05, so five into.

4. Four house with one decimal place

36 or 16 Hex (n_or_s) and inverse hex function unhex (str)

1. Hex-Encode a string

2. Reverse the hexadecimal code

Four, String functions

1.char_length (str) returns the number of characters that the string str contains.

2.CONCAT (STR1,STR2,...) string concatenation function

3. String substitution function INSERT (STR,POS,LEN,NEWSTR)

Starting with the POS position in the string str (calculated from 1), Len-length strings are replaced with NEWSTR

4. String substitution function replace (STR,FROM_STR,TO_STR)

Replace (the source string, the string to replace, and the value to substitute)

5. String fill function Lpad (str,len,padstr) and Rpad (STR,LEN,PADSTR)

Lpad (source string, padding string length, padding string), padstr to the left of STR, if the number of STR is greater than Len, then the concatenation len length string is returned.

Rpad (STR,LEN,PADSTR), which is the right-stitching string

6. Remove the function TRIM ([remstr from] str) for spaces (or specified characters)

TRIM (str) to delete the strings on both sides of STR, and spaces in the middle of the string cannot be deleted.

You cannot delete spaces in the middle of a string

TRIM (' removestr ' from str) removes the string that precedes or follows the REMOVESTR in Str, the middle cannot be deleted, and the space is removed by default.

7. Duplicate string function REPEAT (str,count)

REPEAT (requires duplicate string, repeat Count)

8. String comparison size function STRCMP (EXPR1,EXPR2)

V. Date and Time functions

1. Get the current date and time of the function

As above is a function to get the date and time, I often use sysdate.

2. The following are the time functions

#查询年SELECT year (Sysdate ()), #查询月份数字SELECT MONTH (Sysdate ()), #查询月份英文SELECT MONTHNAME (Sysdate ()), #查询的是几号SELECT Day ( Sysdate ()), #查询的是星期SELECT Dayname (Sysdate ()), #查询现在的小时SELECT HOUR (Sysdate ()) #查询现在的分钟SELECT MINUTE (Sysdate ()); Query the number of seconds now select SECOND (Sysdate ()), #时间转换为秒钟SELECT time_to_sec (Sysdate ()), #秒钟转换为时间SELECT sec_to_time (65393) #日期格式化 http ://www.w3school.com.cn/sql/func_date_format.aspselect date_format (Sysdate (), '%y%m%d ') #时间格式化SELECT TIME_FORMAT ( Sysdate (), '%h%i%s ')

SQL--functions