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)

Example:

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

Example:
select power (7,2)

It returns 49

Example:
select power (2,3)

It returns 8

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

Example:
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

Example:
select sign (-8.6)

It returns -1

Example:
select sign (10.1)

It returns 1

h. Ceiling (n):It returns the smallest integer greater than 'n'

Example:
select ceiling (15.65)

It returns 16

Example:
select ceiling (14.25)

It returns 15

i. Floor(n):It returns the greatest integer lessthan 'n'

Example:
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.

Example:
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 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