# 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
```