# Predefined Functions in SqlServer

Posted by T.saravanan under Sql Server category on | Points: 40 | Views : 5822
Hi All,

Here i am give some Predefined functions and its use.

Mathematical Functions
Abs,Ceiling,Floor,Exp,round

Select abs(-23) // output '23' Get absolute value
Select ceiling(25.3) // output '26' get round the value
Select Floor(45.8) // output '45' get round the value
Select Exp(0) //get a exponential value of '0'
Select Round(145.245874,3) //output 145.246 round a value after the no.of digit.

Trignometric Functions
Sin,Cos,Tan,Log

Select Sin(0) //get sin value of '0'
Select Cos(0) //get cos value of '0'
Select Tan(0) //get tan value of '0'
Select Log(0) //get log value of '0'

String Functions
Ascii,Char,Reverse,SubString,Stuff,Replicate,Replace,Ltrim,Rtrim

Select Ascii('A') // Output '65' get Ascii value of 'A'
Select Char(97) //output 'a' get char value of 97
Select Reverse('sample') //output 'elpmas' get the reverse value of string
Select Substring('Hello World',3,5) //output 'llo w'
Select Stuff('world',2,5,'welcome') //output 'wwelcomorlde' Here 2 --> Start Position 5 --> Replace Char Length
Select Replicate('Hai ',3) //output 'Hai Hai Hai ' its repeat a Value 3 times Here 3 --> No.of Times
Select Replace('abcdefgh','de','xy') //output 'abcxyfgh'
Select Ltrim(' Hai ') //output 'Hai ' remove a space in front of the string
Select Rtrim(' Hai ') //output ' Hai' remove a space in end of the string

Date Functions

Select GetDate() //Get Current Date
Select Datediff(dd,'1985-05-06','2005-06-09') //Get Date difference between those days
Select Datepart(dd,getdate()) //get a date only from currentdate

System Functions
Db_Id,Db_Name,IsDate,Isnumeric

Select db_id('master') // Get order of that Database
Select db_name(4) //Get a 4th order of name in a database
Select isdate('02/31/2005') //Date is availabe or not Here output comes '0' or '1'
Select isnumeric(15s) // Value is mumeric or not Here output comes '0' or '1'

Aggregate Functions
Count,Max,Min,Avg,Sum

Count() --> Get Total Count of Row
Max() --> Get a Maximum value of a Column
Min() --> Get a Minimum value of a Column
Avg() --> Get a Average value of a column
Sum() --> Get Total Value of a Column

I hope this Functions are very useful to Sql beginners.

Cheers :)