07 - Functions for customizing the result set

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:

Fig - Date Function Example

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

Fig - Date function example - 2

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:

Fig - Rank example - 1

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:

Fig - Example of Rank -2

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.

Fig - Example for dense_rank ()

 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:

Fig - Example for dense_rank ()

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:

Fig - Example for ntile()

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:

 

Fig - Example for ntile()

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

 

Like us on Facebook