Functions in SQL Server

Welcome to the SQL Server Database. This article explains how to use built-in functions of SQL Server. No system would function without some kind of standard set of functions that can be used to simplify queries. We'll spend a moment looking at some of the more useful aggregate functions.

What are aggregate functions?

They perform a calculation of a set of values and return a single value in the result. 

The following are some of the aggregate functions we will explain:

1. min()
2. max()
3. count()
4. sum()
5. avg()
6. convert()
7. getdate()
8. datediff()
9. datepart()
10. soundex()
11. substring()
12. upper()
13. lower()
14. rtrim()
15. charindex()

These are the built-in functions. We will explain now one by one.

I used the table as follows:


 
1. min()

It returns the minimum value of a specified column name. It ignores null values.

Syntax

select min(column_name) from database_tableName;

Example

select min(Copies) from [BooksLibrary].[dbo].[TotalBooks]

Result

 
2. max()

It returns the maximum value of a specified column name. It ignores null values.

Syntax
 

select max(column_name) from database_tableName;

Example

select max(Copies) from [BooksLibrary].[dbo].[TotalBooks]

Result

3. count()

It is a very commonly used function. It returns the total number of rows present in the specified column name. It is also used with the where clause. It ignores null values.

Syntax

select count(column_name) from database_tableName;

Example

select count(Copies) from [BooksLibrary].[dbo].[TotalBooks]

Result

  
4. sum()

It returns the sum of the values of the specified column name and returns a single value as the result.
It ignores null values.

Syntax

select sum(column_name) from database_tableName;

Example
 

select sum(Copies) from [BooksLibrary].[dbo].[TotalBooks]

Result

  
5. avg()

It returns the average of the values in a specified column name. It ignores null values.

Syntax

select avg(column_name) from database_tableName;

Example

select avg(Copies) from [BooksLibrary].[dbo].[TotalBooks]

Result

6. convert()

This versatile function converts one data type to another so that a number of operations can be performed. You just specify the data type to which you wish the existing column or expression to be converted.

Syntax

select convert(data_type, expression) from database_tableName;

Example

declare @myval decimal (5, 2);

set @myval = 193.57;

select convert(decimal(5,2), convert(nchar(20), @myval))

Result

7. getdate()

It returns the current date and time from the server's system clock.

Syntax

select getdate();

Example

select getdate();

Result



8. datediff()

You can use this function to compare and return the difference between date items such as days, weeks, minutes, and hours. When this function is used in a WHERE clause, you can return records that meet a range of dates, or that meet certain time-span intervals.

Syntax

select datediff(datepart, startdate, endate);
datepart - Is the parameter that specifies which part of the date to compare and return a value for.

Example

select datediff(day, '2013-10-01', '2013-10-03');

select datediff(month, '2013-10-01', '2013-11-03');

select datediff(year, '2012-10-01', '2013-10-03');

Result



9. datepart()

This function returns a value equal to the part of a date that you specify. If, for instance, you need to know the day of the week of a specific date then you can use this function to quickly pull that data out of a column or variable.

Syntax

select datepart(datepart,date);

datepart - Is the parameter that specifies which part of the date to compare and return a value for.

Example

select datepart(month,getdate());

Result


 
10. soundex()

This function converts a string to a four-digit code that represents the string. If two strings are very close to the same spelling then they will have the same SOUNDEX() value returned. This function can be used to find a list of potential matches for a string in a set of rows.

Syntax
 
select soundex(cahr_expression);

character_expression can be a constant, variable, or column.

Example
 

select soundex('krishn');

Result



11. substring()

The substring() function is used many times throughout this book. Any string manipulation can be accomplished with this function in conjunction with a few string operators and some other basic string functions.

Syntax

select substring(char-expression, start_position, length);

character_expression can be a constant, variable, or column of either 
character or binary data.

Example

select substring('krishn', 2, 3);

Result



12. upper()

upper() converts the string passed to the function into all uppercase characters. You can use this function to maintain the data integrity of text columns in your tables without the user or client intervening.

Syntax

select upper(char-expression);

character_expression can be a constant, variable, or column of either character or binary data.

Example

select upper('krishn');

13. lower()

lower() converts the string passed to the function into all lowercase characters. You can use this function to maintain the data integrity of text columns in your tables without the user or client intervening.

Syntax

select lower(char-expression);

character_expression can be a constant, variable, or column of either character or binary data.

Example  

select lower('KRISHN');

14. rtrim()

rtrim() removes any trailing blanks from a string or column. In some situations, this helps keep the formatting and reporting working the way your applications expects. rtrim() is most useful in text reports generated from raw SQL.

Syntax

select rtrim(char_expression);

Example

select rtrim('Hare krishn.  ');

Syntax of ltrim()

select ltrim(char_expression);

Example

select ltrim('  Hare krishn.');

15. charindex()

This function can be used to search for a match for a string in a column. If you want the character offset of a string within a string then this function returns the corresponding numbered offset of the start of the match. If you use this function to search an entire table then it will return a result set with a non-zero value for each row that contains a string.

Syntax

select charindex(expressionToFind ,expressionToSearch [ , start_location ] )

expressionToFind - Is a character expression that contains the sequence to be found.
expressionToSearch - Is a character expression to be searched.
start_location - starting index if u know.

Example

select charindex('krishn','Hare krishn');

Result


 
This article is all about the built-in functions in SQL Server databases.