7.1. Aggregate Functions

 

  • SQL Server has many built-in functions.
  • This reference contains string, numeric, date, conversion, and some advanced functions in SQL Server.


SQL Server String Functions


Function Description QUERY
ASCII Returns the ASCII value for the specific character. select car_model_name, ASCII (car_model_name) AS anything from Car ;
CHAR Returns the character based on the ASCII code. select CHAR (76) as characte;
CHARINDEX Returns the position of a substring in a string. select charindex ('l','ballolns',5);
CONCAT Adds two or more strings together. select CONCAT('hello','User'); select 'hello'+'user';
DATALENGTH Returns the number of bytes used to represent an expression. select DATALENGTH('hello user');
DIFFERENCE Compares two SOUNDEX values, and returns an integer value. select DIFFERENCE('hlleo','hlleo');
FORMAT Formats a value with the specified format. select FORMAT (123456789,'##-###-##-##');
LEFT Extracts a number of characters from a string (starting from left). select company_name, left (company_name,4) as string from car ;
LEN Returns the length of a string. select company_name, len (company_name) as string from car ;
LOWER Converts a string to lower-case select company_name, lower (company_name) as string from car ;
LTRIM Removes leading spaces from a string. select rtrim(LTRIM(' hello ')) as trim;
NCHAR Returns the Unicode character based on the number code. select NCHAR(75) as unicod;
PATINDEX Returns the position of a pattern in a string. select company_name,PATINDEX ('%m%',company_name) from car;
QUOTENAME Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited. select QUOTENAME('abcdefgh','{}');
REPLACE Replaces all occurrences of a substring within a string, with a new substring. Select company_name,REPLACE (company_name,'C','M') as replacevalue from Car;
REPLICATE Repeats a string a specified number of times. select REPLICATE(company_name,10) from Car;
REVERSE Reverses a string and returns the result. select company_name, REVERSE(company_name) as revers from car;
RIGHT Extracts a number of characters from a string (starting from right). select RIGHT ('helloman',5);
SUBSTRING Extracts some characters from a string. select company_name , SUBSTRING(company_name,2,4) as substin from Car;
 UPPER  Converts a string to upper-case.  select upper(c_name) from car;



SQL Server Date Functions


Function Description QUERY
DATEDIFF Returns the difference between two dates. SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff;
GETDATE Returns the current database system date and time. SELECT GETDATE as current date;
DATEADD Adds a time/date interval to a date and then returns the date. SELECT DATEADD(year, 1, '2017/08/25') AS DateAdd;
DATEPART Returns a specified part of a date (as integer). SELECT DATEPART(year, '2017/08/25') AS DatePartInt;
DAY Returns the day of the month for a specified date. SELECT DAY('2017/08/13 09:08') AS DayOfMonth;
MONTH Returns the month part for a specified date (a number from 1 to 12). SELECT MONTH('2017/08/25') AS Month;
 YEAR  Returns the year part for a specified date.  SELECT YEAR('2017/08/25') AS Year;



SQL Server Math and Aggregate Functions:


Function Description QUERY
AVG Returns the average value of an expression. SELECT AVG(Price) AS AveragePrice FROM Products;
COUNT Returns the number of records returned by a select query. SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
MAX Returns the maximum value in a set of values. SELECT MAX(Price) AS LargestPrice FROM Products ;
MIN Returns the minimum value in a set of values. SELECT MIN(Price) AS SmallestPrice FROM Products;
POWER Returns the value of a number raised to the power of another number, it is not aggregate function. SELECT POWER(4, 2);
SQRT Returns the square of a number, it is not aggregate function. SELECT SQRT(64);
SQUARE Returns the square of a number, it is not aggregate function. SELECT SQUARE(4);

Last modified: Thursday, 5 December 2019, 5:19 PM