Predefined Functions in sql server

Sql server provides predefined functions which are collectively call it as a built in functions of sql server are classified into

    Scalar function

    Aggregate function

    Ranking function

Scalar function

Are the built in function that operate on single value based on the data type on which scalar function operate , they are classified into

  Numeric function

  Character function

  Conversion function

Numeric function or mathematical functions

Abs(n)

That returns the absolute (positive) value of the specified numeric expression.

select abs(-76.54)

output:

76.54

select abs(-5.344)

output:

5.344

select abs(-10)

output: 10

select abs(sal) from emp

Floor (n)

Used to round the given n value to the nearest integer less than or equal given n.

select FLOOR(7.643)

output:

7

select FLOOR(-7.643)

output: -8

Ceiling(n)

Used to round the given n value to the nearest integer greater than or equal given n

ceiling(n): used to round the given  n value to the nearest integer greater than or equal given n

select CEILING(6.549)

output: 7

ROUND(n,p[,t])

used to round or truncate the given n value to p decimal places

examples

select ROUND(74.5471,2)

output: 74.5500

select round(156.567,2)

output: 156.570

select round(156.567,1)

output: 156.600

Square

Returns square of a Number

Select square(25)

Output:625

Power()

Power(m,n)

Used to calculate  power

Select power(3,3)

Output:9

Sqrt

Returns square root of given n

Select sqrt(625)

Output:25

Exp(n)

Returns exponential value of given n that is the scientific constant e raised to the power of n

Exp(n)

-e =2.718

Select exp(3) (-2.718) to the power 3

Output:20.0855

Log(n)

Returns natural logarithm of given n

Eg log(2)

0.693

Log 10(n)

Returns based 10 logarithm value of given n

Eg log10(2)

0.3010299

Radians(n)

Used to convert given n in degrees to radians

Select radians(30.00)

Degree(n)

Used to convert the given n in radiant to degrees

Select degrees(0.523555555)

Sin(n)

Returns the sin value of given n in radiants

Select sin(radians(30.00))

They will take n in radians

Output:0.5

Cos(n)

Returns the cos value of given n in radiants

Select cos(radians(30.00))

Output: 0.86

Tan(n)

Returns the tangent value of given n in radiants

Example

Tan(radians(30.00)

Output:0.577

Sign(n)

This function return zero if the given n is zero

-1 if the given n is negative and

+1 if the given n is positive.

If  n<0 it returns -1

If n=0 it returns 0

If n>0 it returns 1

Select sign(0)

Output:0

Sign(7)

Output 1

Select sign(-7)

Output -1

Pi

Select pi()

Output:3.14

Character functions or string functions

Are the built in functions that operate on character type of data.

Character functions available in sql server are as follows

Ascii(char)

Returns ascii value of given character

Ascii(‘a’)

Output: 97

Char(n)

Returns character for the given ascii value

Select char(65)

Output A

Substring(String,p,n)

Used to extract n number of characters starting from position p within the given string

Select substring(‘MICROSOFT’,3,4)

Output: cros

Select substring(‘Hello’,3,3)

Output: LLO

Left(string,n)

Used to extract n no of characters from the left of the given string

Select left(‘Hello’,3)

Write a query to get the details of employee whose name contains the first 2 characters as ‘VE’

Select * from emp where left(ename,2)=’VE’

Write a query to get the dtails of employee whose name starts with ‘s’

Select * from emp where left(ename,1)=’s’

Right(string,n)

Used to extract n no of characters from the right of the given string

Select right(‘microsoft’,4)

Output: soft

Select right(‘hello’,3)

Output: llo

Write a query to get the details  of employee whose name ends with character ‘TT’

Select * from emp where right(ename,2)=’TT’

Write a query to get the details of employees whose name 3 rd and 4 rd character are ‘TI

Select * from emp where right(left(ename,4,2)=’TI’

OR

Select * from emp where substring(ename,3,2=’TI’

Stuff(string1, p, n string2)

Used to replace n no of characters starting from position p in string1 with string2

Select stuff(‘Notepad’,1,4,’Word’)

Output: wordpad

Len(String)

Returns no of characters in the given string

Find the employees whose name contains exactly 4 characters

Select * from emp where len(ename)=4

Select len(‘hello’) output 5

Select len(‘   hello’) output 8

Reverse(string)

Used to reverse the given string

Select ename,reverse(ename) from emp

Nchar(n)

Returns the Unicode character with the specified integer code ranging between 0 t0 65,535 as defined by Unicode standard

charIndex(string1,string2[,p])

used to search for sting1 in string 2 and returns a positive value, if string1 was found in string2 and otherwise returns zero

select charIndex('o','hello world')

output 5

here the third argument specifies the position from where to start the search ,which is by default set to 1

select charIndex('or','corporation')

output: 2

select charIndex('o','hello world',6)

output: 8

find the employees whose name contains alphabet a exactly twice in the name

select * from emp where CHARINDEX('a',ename,charindex('a',ename)+1)>0

upper(String)

Returns a character expression after converting the given character data into upper case

select upper('hello')

output: HELLO

lower(string)

returns a character expression after converting the given data into lower case

select lower('HELLO')

OUTPUT: hello

RTRIM(S)

used to delete all blank spaces available on the right of the given string

select RTRIM('HELLO ')+   'WORLD'

output: HELLO WORLD

LTRIM(S)

Used to delete all blank spaces available on the left of the given string

select len(LTRIM(  'hELLO')

output: 5

space()

this function is used to generate "spaces"

select 'hello' +space(5) +'welcome'

output: hello   welcome

Replicate(s,n)

Repeats the expression 's' for specified 'n' no of times.

select Repicate('HEL',2)

OUTPUT: HELHEL

Replace()

Is used to replace one string with another string.

syntax:

Replace(string,search string,replace string)

select replace('hello','ell','abc')

output: habco

soundex(s)

returns a hexadecimal value indicating how the given string will be prounced

find the employee whose name is 'scott'

select * from emp where soundex(ename)=soundex('skott')