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 nceiling(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')
