Functions in SQL - Scalar - Mathematical, String, Date and Time functions

A function is a predefined programming segment specially used for specific and well defined task.

Functions in SQL Server:

     1. Scalar functions
     2. Aggregate or group functions

1. Scalar functions:

These scalar functions will take single value as input and returns a single value.
  • Mathematical Functions
  • String functions
  • Date time functions
  • System functions
  • Security functions
  • Confirmation functions
  • Cursor functions
  • System statistic functions
  • Text and image functions
We will concentrate on first three functions now.

1. Mathematical Functions:

a. absolute(n): It returns the absolute value of the number (n)

select abs(-10.25)

It will return 10.25
select abs(9.25)

It will return 9.25

b. PIT(): It returns the constant value of PI (22/7)

select pi()

It will return 3.141

c. Degrees: It converts the radians into degrees.

select degrees(PI())

It will return 180 degrees

select degrees (PI()/4)

It will return 44 degrees

d.Sqrt(n): It returns the square root value of 'n'.

select sqrt(2)

It will return 1.414
select sqrt(9)

It will return 3
e. power (m,n):It returns the m power n value
select power (7,2)

It returns 49
select power (2,3)

It returns 8
f. Square(n): It returns the square value of 'n'
select square (2)

It returns 4
g. sign (n): It returns '1' if n is positive '-1' if n is negative '0' if n is zero
select sign (-8.6)

It returns -1
select sign (10.1)

It returns 1
h. Ceiling (n):It returns the smallest integer greater than 'n'
select ceiling (15.65)

It returns 16
select ceiling (14.25)

It returns 15
i. Floor(n):It returns the greatest integer lessthan 'n'
select floor (15.65)

It returns 15
g. Round (m,n):It will return the value of m to the nearest whole number (or) It will round the value 'm' based on value of 'n'. n- indicates number of digits right to the decimal point.
select round (8.134,0)

It returns 8
select round (6.534,0)

It returns 7
select round (15.134,0)

It returns 15.1
select round (8.5561,1)

It returns 8.6
select round (18.8761,2)

It returns 18.88
select round (04.5561,-1)

It returns 0.0
select round (14.123,-2)

It returns 0.0
Truncate is valid only in oracle but not in SQL Server.

2. String Functions:

a. Ascii('Char'): It returns the ascii value of a character.

Select ascii('a')

It returns 97
Select ascii('o')

It returns 48
Select ascii('NULL')

It returns NULL
Select ascii('b')

It returns 32

b. Char(n): It returns the character for the given number.

Select char(48)

It returns o
Select char(97)

It returns a
c. Lower('String'): It converts out the uppercase strings into lower cases.
Select lower('ORACLE')

It returns oracle
d. Upper('String'):It converts out the lowercase strings into upper cases.

Select upper('oracle')

It returns ORACLE
e. Length ('string') or Len ('string'):It returns the length of a give string. space also considered as character.
Select length('ORACLE')

It returns 6
f. Replace('string','searching string','replace string'): It will replace a sub string with another string.
select replace('Jack is going','J','B')

It returns Back is going
g. Reverse('String'):It will reverse the given string.
select reverse ('oracle')

It returns the elcaro
h. Substring('String',startingposition,number of characters requried): It is useful to extract a substring from a main string.
select substring ('oracle', 1, 2)

It returns the or
select substring('oracle', 2)

It returns the racle
It will take entire string from 2nd letter on wards as no of character value is omitted.

3. Date and Time functions:

date part and their abbreviations
Date Part Abbreviations
1. getdate(): It returns the server or system date.

select getdate()

It returns the 2003-04-21  12:15:30:000
sIt returns the new date value after adding 'm' given date according to datepart.
select dateadd(year,1, '2003-10-26')

It returns the 2004-10-26  
select dateadd(month,1, '2003-10-26')

It returns the 2003-11-26  
3. datediff(datepart, date1, date2):It returns the difference between the two dates according to datepart.
select datediff(month,'2003-09-26', '2003-10-26')

It returns the 1 ie., 1 month 
select datediff(days,'2003-09-26', '2003-10-26')

It returns the 30 ie., 30 days

1 comment: