SQL Server (v)--common functions

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 ') to 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 select left (' 123456 ', 3) from the opening

--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); Convert 1.567 to String, 3 for--up to 3 digits, one for decimal point, 1 for--reserved 1 digits after 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 ');

Note: DatePart can return a few weeks, but the type of int is returned

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 ');

SQL Server (v)--common functions