Functions In SSQL SERVER
We have 2 types of functions in sql server.They are
1.System Functions---Builtin functions
2.Userdefined Functions
We can differentiate built-in functions Following .
1.Single Row Functions
2.Group Functions
Singlerow Functions
Ø Mathematical Functions
Ø Strimg Functions
Ø Date and Time functions
Matheatical Functions
1.ABS
select ABS(10)
select ABS(-10)
Select ABS(0)
2.Ceiling
Select Ceiling(15.6)
Select Ceiling (15.2)
Select Ceiling(15.0)
3.Floor
Select floor(15.6)
Select floor(15.2)
Select floor(15.0)
4.Log(n)
Select log(10)
Select log(2)
5.Log10
Select log10(10)
Select log10(2)
6.PI
Select PI()
7.Power(n,m)
Select POWER(10,3)
Select POWER(2,3)
8.RAND([SEED])
Select RAND()
Select RAND(100)
9.ROUND(n,length,[,Function])
Select ROUND(1546.561,2)
Select ROUND(1546.561,1)
Select ROUND(1546.561,0)
Select ROUND(1546.561,-1)
Select ROUND(1546.561,2,1)
Select ROUND(1546.561,-2,1)
10.SIGN(n)
Select SIGN(100)
Select SIGN(0)
Select SIGN(-100)
11.SQRT(n)
Select SQRT(25)
12.SQUARE(n)
Select SQUARE(5)
13.In Addition we have some tagnometric Functions like SIN,COS,TAN,ACOS,ASIN,ATAN
String Functions
1.ASCII(s)
Select ASCII(‘A’)
2. CHAR(‘n’)
Select CHAR(‘100’)
3. NCHAR(’n’)
Select NCHAR(‘97’)
4.CHARINDEX(SearchExp,StringExp,[Start location])
Select CHARINDEX(‘o’,’HELLO’,2)
5.LEFT(s,n)
Select LEFT(‘HELLO’,3)
6.RIGHT(S,N)
Select RIGHT(‘HELLO’,3)
7.SUBSTRING(S,Start,Length)
Select SUBSTRING(‘HELLO’,1,5)
8.LEN(S)
Select LEN(‘KRISH’)
9.LOWER(S)
Select LOWER(‘kRIsh’)
10.UPPER(S)
Select UPPER(‘JaNu’)
11.LTRIM(S)
Select LEN(LTRIM(‘ HELLO’))
Select ‘Hello’+LTRIM(‘ WORLD’)
12.RTRIM(S)
Select LEN(RTRIM(‘ HELLO ’))
Select ‘Hello’+RTRIM(‘ WORLD ’)
13.REPLACE(S1,S2,S3)
Select REPLACE(‘HELLO’,’L’,’X’)
14.REPLICATE(S,N)
Select REPLICATE(‘KRISH’,3)
15.REVERSE(S)
Select REVERSE(‘HELLO’)
16.SOUNDEX(S)
Select SOUNDEX(‘Smith’),SOUNDEX(‘Smyth’)
17.DIFFERENCE(S1,S2)
Select DIFFERENCE (‘SMITH’,’SMYTH’)
18.SPACE(n)
Select ‘Hello’ + SPACE(3)+’WORLD’
19.STUFF(S,Start,Length,Replace-Str)
Select STUFF(‘ABXXCDXX’,3,3,’yy’)
Date and Time Functions
1.GETDATE()
Select GETDATE()
2.DAY(DATE)
Select DAY(GETDATE())
Select DAY(‘05/05/2010’)
3.MONTH(DATE)
Select MONTH(GETDATE())
Select MONTH(’05/04/87’)
4.YEAR(DATE)
Select YEAR(GETDATE())
Select YEAR(‘05/07/1987’)
5.DATENAME(Datepart,date)
Select DATENAME(mm,’10/24/78’)
Select DATENAME(dd,’10/24/78’)
6.DATEPART(Datepart,Date)
7.DATEADD(Datepart,number,date)
Select DATEADD(DD,30,GETDATE())
Select DATEADD(MM,10,GETDATE())
8.DATEDIFF(DATEPART,STARTDATE,EndDate())
Select DATEDIFF(yy,’10/04/1987’.,05/01/2011’)
9.GETUTCDATE()
Select GETUTCDATE()
Conversion Functions
1.CAST(Expression AS DataType[(Length)]
Select CAST(10.6496 AS INT)
Select CAST(10.349787 AS MONEY)
2.CONVERT(DataType[Length],Expression [,Style])
Select CONVERT(INT,10.67789)
Select CONVERT(VARCHAR(50),GETDATE())
Select CONVERT(VARCHAR(50),GETDATE(),101)
System Functions
1.ISNUMERIC (EXPRESSION)
SEELCT ISNUMERIC(100)
SELECT ISNUMERIC(‘100’)
SELECT ISNUMERIC(‘100A’)
2.ISDATE(EXPRESSION)
SELECT ISDATE(’15/14/2011’)
3.ISNULL(EXPRESSION1,EXPRESSION2)
SEELCT ISNULL(100,200)
SEELCT ISNULL(NULL,200)
4.COALESCE(EXPRESSION1,EXPRESSION2,………EXPRESSIONN)
SEELCT COALESCE(NULL,100,NULL,200)
5.DATALENGTH(EXPRESSION)
SELECT DATALENTH(100)
SELCT DATALENGHT(‘HELLO’)
6.HOST_NAME()
SELECT HOST_NAME()
7.IDENT_CURRENT(‘TABLENAME’)
SEELCT IDENT_CURRENT(‘BANK’)
8.IDENT_SEED(‘TABLENAME’)
SELECT IDENT_SEED(‘BANK’)
9.IDENT_INCR(‘TABLENAME’)
SEELCT IDENT_INCR(‘BANK’)
10.NEWID()
SELECT NEWID()
11.NULLIF(EXPRESSION1,EXPRESSION2)
SELECT NULLIF(100,200)
SELECT NULLIF(100,100)
13.APP_NAME()
14.CASE
GROUP FUNCTIONS
1.COUNT(EXPRESSION)
SELECT COUNT(*) FROM EMP
2.SUM(EXPRESSION)
SELECT SUM(SAL) FROM EMP
3.AVG(EXPRESSION)
SELECT AVG(SAL) FROM EMP
4.MAX(EXPRESSION)
SEELCT MAX(SAL) FROM EMP
5.MIN(EXPRESSION)
SEELCT MIN(SAL) FROM EMP
6.STDEV(EXPRESSION)
