SQL Common functions

Source: Internet
Author: User
Tags add time first string getdate

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.