Скачать или смотреть 3. SQL Functions

FUNCTIONS:

String Functions:

CHARINDEX :
Search for "t" in string "Customer", and return position:
SELECT CHARINDEX('t', 'Customer') AS MatchPosition;

CONCAT :
Add two strings together:
SELECT CONCAT('srinivas', 'R');

CONCAT_WS :
Add strings together. Use '.' to separate the concatenated string values:
SELECT CONCAT_WS('.', 'abc', 'bcd', 'efg');

DATALENGTH :
Return the length of an expression (in bytes):
SELECT DATALENGTH('pvrcloudtech.com');

LEFT :
Extract 3 characters from a string (starting from left):
SELECT LEFT('SQL Tutorial', 3) AS ExtractString;
LEN :
Return the length of a string:
SELECT LEN('pvrcloudtech.com');

LOWER :
Convert the text to lower-case:
SELECT LOWER('SQL Tutorial');
LTRIM :
Remove leading spaces from a string:
SELECT LTRIM(' SQL Tutorial') AS LeftTrimmedString;

REPLACE :
Replace "T" with "M":
SELECT REPLACE('SQL Tutorial', 'T', 'M');

REPLICATE :
Repeat a string:
SELECT REPLICATE('SQL Tutorial', 5);

REVERSE :
Reverse a string:
SELECT REVERSE('SQL Tutorial');

RIGHT :
Extract 3 characters from a string (starting from right):
SELECT RIGHT('SQL Tutorial', 3) AS ExtractString;
RTRIM :
Remove trailing spaces from a string:
SELECT RTRIM('SQL Tutorial ') AS RightTrimmedString;

SUBSTRING :
Extract 3 characters from a string, starting in position 1:
SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString;

TRIM :
Remove leading and trailing spaces from a string:
SELECT TRIM(' SQL Tutorial! ') AS TrimmedString;

UPPER :
Convert the text to upper-case:
SELECT UPPER('SQL Tutorial is FUN!');

Numeric Functions:
====================

ABS :
Return the absolute value of a number:
SELECT Abs(-243.5) AS AbsNum;
AVG :
Return the average value for the "Price" column in the "Products" table:
SELECT AVG(Price) AS AveragePrice FROM Products;
CEILING :
Return the smallest integer value that is greater than or equal to a number:
SELECT CEILING(25.75) AS CeilValue;
COUNT :
Return the number of products in the "Products" table:
SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
COS :
Return the cosine of a number:
SELECT COS(2);
COT :
Return the cotangent of a number:
SELECT COT(6);


FLOOR :
Return the largest integer value that is equal to or less than 25.75:
SELECT FLOOR(25.75) AS FloorValue;
LOG :
Return the natural logarithm of 2:
SELECT LOG(2);
LOG10 :
Return the base-10 logarithm of 2:
SELECT LOG10(2);
MAX :
Find the price of the most expensive product in the "Products" table:
SELECT MAX(Price) AS LargestPrice FROM Products;
MIN :
Find the price of the cheapest product in the "Products" table:
SELECT MIN(Price) AS SmallestPrice FROM Products;
PI :
Return the value of PI:
SELECT PI();
POWER :
Return 4 raised to the second power:
SELECT POWER(4, 2);
ROUND :
Round the number to 2 decimal places:
SELECT ROUND(235.415, 2) AS RoundValue;
SIGN :
Return the sign of a number:
SELECT SIGN(255.5);
SIN :
Return the sine of a number:
SELECT SIN(2);
SQRT :
Return the square root of a number:
SELECT SQRT(64);
SQUARE :
Return the square of a number:
SELECT SQUARE(64);
SUM :
Return the sum of the "Quantity" field in the "OrderDetails" table:
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

Date Functions:
======================

CURRENT_TIMESTAMP :
Return the current date and time:
SELECT CURRENT_TIMESTAMP;
DATEADD :
Add one year to a date, then return the date:
SELECT DATEADD(year, 1, '2017/08/25') AS DateAdd;
DATEDIFF :
Return the difference between two date values, in years:
SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff;
DATEFROMPARTS :
Return a date from it's parts:
SELECT DATEFROMPARTS(2018, 10, 31) AS DateFromParts;
DATENAME :
Return a specified part of a date:
SELECT DATENAME(year, '2017/08/25') AS DatePartString;
DATEPART :
Return a specified part of a date:
SELECT DATEPART(year, '2017/08/25') AS DatePartInt;
DAY :
Return the day of the month for a date:
SELECT DAY('2017/08/25') AS DayOfMonth;
GETDATE :
Return the current database system date and time:
SELECT GETDATE();
GETUTCDATE :
Return the current UTC date and time:
SELECT GETUTCDATE();
ISDATE :
Check if the expression is a valid date:
SELECT ISDATE('2017-08-25');
MONTH :
Return the month part of a date:
SELECT MONTH('2017/08/25') AS Month;
SYSDATETIME :
Return the date and time of the SQL Server:
SELECT SYSDATETIME() AS SysDateTime;
YEAR :
Return the year part of a date:
SELECT YEAR('2017/08/25') AS Year;