Common functions for SQL programming

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

-Pi (), pi, brackets, no need to add anything.

--round rounding
Select ROUND (3.76,0)

--SQRT Open Radicals

--square squared, multiplied by himself

2. String functions:

--Convert Capital Upper
Select Upper (pic) from car;

--Convert lowercase lower

--Go to space
Select LTrim (' 123 ') go left space

Select ' 123123 ' can be displayed directly without checking the data.

--space () put a few numbers inside, 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 the 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);
Intercept 3 bits starting from 2nd, index starting from 1

3. Time-Date function:

--Get current system time GetDate ()
Select GETDATE ();

Sysdatetime () Gets the timestamp of 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

--Returns the day of the week datename, the value returned is a string
Select Datename (Weekday, ' 2000-1-1 ');

You can also return the day of the week, by 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

Common functions for SQL programming