1. Math function : Manipulate one data and return a result
--Take the upper limit ceiling
Select Code,name,ceiling (price) from car;
--Lower floor
Select Floor, from car
--abs Absolute Value Rounding
-Pi (), pi, brackets, no need to add anything.
--round rounding
Select ROUND (3.76,0)--, the front is written to get the decimal, and the following write retains several decimals starting from the first round
--SQRT Open Radicals
--square squared , multiplied by himself
2. String functions:
--Convert Capital Upper
Select Upper (pic) from car;
--Convert lowercase lower
--Go to the space LTrim left space RTrim go to the right space
Select ltrim (' 123 ') go left space
Select ' 123123 ' can be displayed directly without checking the data.
--space ()
Put a few numbers in it and print out a few spaces.
--left, similar to substring, intercepts from the left
Select Left (' 123456 ', 3);
--len, length
Select Len (' aaaaaa '); return several lengths
--replace replacement
Select replace (' aaaaabbaaaaa ', ' BB ', ' haha ');
Replace the BB in the first string with a haha
--reverse Flip
Select reverse (' abc '); The result is CBA
--String conversion function str
Select STR (1.567,3,1);
Converts 1.567 to a string, leaving up to 3 digits, one decimal point, and 1 digits after the decimal point
--String intercept SUBSTRING
Select substring (' ABCDEFG ', 2, 3); The result is a BCD
Intercept 3 bits starting from 2nd, index starting from 1
3. Time-Date function:
--Get current system time GetDate ()
Select GETDATE ();
Sysdatetime () time to get the database service
--Get Month day
Select year (' 1999-1-1 ');
--Determine if the date is correct, isdate return bit
Select IsDate (' 2000-2-31 ') returns the bit type, False is 0,true is 1
--Add Time DateAdd
Select DATEADD (year,5, ' 2000-1-1 ');
Add what type, plus how much, to who adds
--Returnsthe day of the week datename, the value returned is a string
Select Datename (weekday, ' 2000-1-1 ');
It is also possible to return the day ordinal of the month
Select Datename (Day, ' 2000-1-1 ');
The first day of the year
Select Datename (dayofyear, ' 2000-1-1 ');
DatePart can return a few weeks, but it returns an int type
SQL Common functions