There are several built in functions provided by the SQL Server for customizing the result set. The format of the values can be changed by applying respective functions.
The following is a list of various functions offered by SQl Server
- String Functions
- Date and Time Functions
- Mathematical Functions
- Aggregate Functions
- Configuration Functions
- Metadata functions
- Security Functions
- System Functions
- Ranking Functions
Now let’s discuss some of the functions.
7.1 Using String Functions
The string functions are used on char and varchar data types. These functions are used to manipulate the string values in a result set.
The following is the syntax to use this function:
SELECT function_name (parameters)
Where,
function_name is the name of the function
and parameters are the required parameters for the string function
Examples of some of the string functions provided by the SQL Server are as follows:
Function Name | Example | Description |
ascii | SELECT ascii ( ‘AB’) | It returns the numeric value of left-most character. In this se it returns the value of A as 65. |
char | SELECT char (66) | Returns B, the character equivalent to the ASCII code |
len | SELECT len ( ‘WIDESKILLS’) | Returns 10 as the number of characters present in the expression |
lower | SELECT lower ( ‘JOHN’ ) | Returns ‘john’ after converting the expression into lower case |
replace | SELECT REPLACE ( ‘country’, ‘y’, ‘ies’ ) | Returns ‘countries’ after replacing the ‘y’ with ‘ies’ |
reverse | SELECT reverse ( ‘PATH’) | Returns ‘HTAP’ after reversing the expression |
str | SELECT str (134.56, 6, 2) | Returns ‘134.56, converts numeric to character |
upper | SELECT upper ( ‘Peter’) | Returns ‘PETER’ by converting the characters to upper case |
The code sample for the string function is as shown below:
1) select REVERSE (‘ACTOR’)
2) select upper(‘nitin’)
3) select len (‘MARK’)
7.2 Using Date Functions
The date functions can be used to manipulate the date and time values in SQL server. The date values can be parsed during the arithmetic operations. Date parsing includes extracting values such as day, month, and year from the given data value.
The getdate() function is used to retrieve the current system date. The query for displaying the system date is as follows:
SELECT getdate ()
The following table lists the date functions provided by the SQL Server.
Function Name | Example | Description |
Dateadd | SELECT dateadd (mm, 2, ‘2010-02-03’) | Returns 2010-04-03 by adding 2 months |
Datediff | SELECT datediff ( year, convert (datetime, ‘2006-05-06’), convert ( datetime, ‘2009-01-01’)) | Returns 5, calculating the number of date parts between two dates |
datepart | SELECT datepart (mm, ‘2008-01-01’) | Retruns 1, the month value |
Day | SELECT day ( ‘2010-03-21’) | Returns 21 as an integer that represents the day |
Month | SELECT month (‘2007-04-03’) | Returns 4 as an integer that represents the month |
Year | SELECT year ( ‘2011-04-17’) | Returns 2011, as the integer representing the year |
Conider an examdata table consisting of four columns as subid, subname,examdate and location. The table with the values is as shown below:
Consider a query where user wants the year of an examdate. User wants only the year part of the date. The query for the above condition is as follows:
select subid, DATEPART(yy,’2010-04-05’) as year from examdata
7.3 Using Mathematical Functions
The mathematical functions are used to manipulate the numeric values in the result set. Various arithmetic operations can be performed on the numeric values.
The following table lists some of the mathematical functions that can be used in SQL Server.
Function Name | Example | Description |
abs | SELECT abs (-77) | Returns 77, an absolute value. |
cos,sin,tan,cot | SELECT sin(1.5) | Returns the angle in radians. |
ceiling | SELECT ceiling (14.45) | Returns 15, the smallest or greater than or equal to the specified value. |
exp | SELECT exp (4.5) | Returns 90.017, the exponential value of the specified parameter. |
floor | SELECT floor (15.55) | Returns 15, the largest or less than or equal to the specified value. |
log | SELECT log (5.4) | Returns 1.6869, the logarithm of the parameter. |
Consider an example of an employee table in the database. The Employee table consists of three columns as empid, designation and hourrate. The following snapshot shows the table with the values.
Consider the following query consisting of the mathematical function.
select empid, ‘Hourly Rate’=ROUND(hourrate,2) from employee1
7.4 Using Rank Functions
The functions are used to generate sequence numbers for each row for providing a rank based on the specified condition. The functions are provided to rank the records in the table. The following functions are provided for the rank records.
1) row_number()
2) rank ()
3) dense_rank ()
4) ntile ()
1) row_number()
The function returns the sequence of numbers for each row based on the column in the result set. Each row has a number starting from 1.
Consider an example of result table. It consists of three columns as studid, course and grade. The table containing the values is as shown below:
The following query displays the sequential number on a column by using the ROW_NUMBER() function.
select studid, grade, ROW_NUMBER() OVER ( ORDER BY studid desc) as rank from result
The output for the above query is as shown below:
2) rank()
The function is used to return the rank of each row in a result set based on the specified condition. The ORDER BY clause is used with the function for the purpose of rank.
Consider an example of rate table consisting the columns as orderid, productname, and cost. The table in the SQL server is as mentioned below:
The query for use of rank() function is as shown below:
select orderid, cost, RANK() over ( order by orderid) as rank from rate
The output for the query is as shown below:
3) dense_rank ()
The function is used where consecutive ranking values are to be provided on the specified criteria. It performs similar to the rank function but provides consecutive ranking values as output.
Consider the rate table with columns as orderid, productname and cost.
Apply the following query with the dense_rank() function. The query is as mentioned below:
select orderid, productname, dense_rank() over (ORDER BY orderid desc) as rank from rate
The output for the query is as shown below:
4) ntile()
It is used to divide the result set into number of groups. The function accepts the positive integer to distribute the rows into the number of groups. The groups has the value as 1.
Consider the examdata table with four columns as subid, subname, examdate and location as shown below:
The query using the ntile function is as shown below:
select subid, subname, examdate, NTILE(2) over ( order by examdate ) as rank from examdata where DATEPART ( YY, examdate ) > = 2011
The output for the query is as shown below:
7.5 Using System Functions
The system function are used to query the system tables. They are used to access the server databases and user related information. Consider the following query.
select HOST_ID() as ‘HOSTID’
The output for the query is as shown below:
Some of the system functions are as mentioned below:
Functions | Description | Example |
Host_name | Returns the current computer name | SELECT host_name as ‘HOSTNAME’ |
suser_id ([login_name)] | Returns the log on Identification number | SELECT suser_id( ‘sa’) as SID |
user_id ([name_in_db)] | Returns the database identification number corresponding to the user | SELECT user_id (‘Sam’) as USERID |
db_name ([db_id]) | Returns the database name | SELECT db_name(16) as Databasename |