Built In Functions

Built In Functions

            As in other languages like C and C++, SQL also provides Built in functions. Built in functions in SQL are classified into following two categories.

1.      Scalar Functions

2.      Aggregate Functions

3.      Ranking Functions

I.                   Scalar Functions : The built in functions in SQL Server that work on a single value are called as scalar functions. Scalar functions are again classified into following categories based on data type on which they work.

a)      Numeric Functions

b)      String Functions

c)      Date Functions

d)     Conversion Functions

e)      System Functions

a)       Numeric Functions : The scalar functions of SQL that work on numeric type of data are called as numeric functions. Numeric functions in SQL are as follows.

1. Abs(n) : Returns absolute value of the given n i.e. n value without any sign.
2. Cos(n) : Returns cosine value of given n in radians.
3. Ceiling(n) : Returns smallest integer greater than or equal to n.
4. Floor(n) : Returns Largest integer less than or equal to n.
5. Radians(n) : Converts the given n in degrees to radians.
6. Degrees(n) : Converts the given n in radians to degrees.
7. Exp(n) : Returns e raised to the power of n. e is exponential whose value is constant and is 2.713
8. Log(n) : Returns natural logarithm of n. i.e. base e logarithm.
9. Log10(n) : Returns base 10 logarithm of n.
10.  Power(m,n) : Returns m raised to the power of n.
11. Square(n) : Returns Square of given n.

1. Sqrt(n) : Returns Square Root of given n.
2. Sin(n) : Returns sine value of given n in radians.
3. Tan(n) : Returns tangent value of given n in radians.
4. Sign(n) : Return -1 if n is negative, 1 if n is positive and 0 if n is zero.
5. Pi() : Return mathematical constant pi value i.e. 3.14
6. Round(n,p,t) : Rounds the given in n to p decimal places. If t is specified as 1 then it truncates n to p decimal places instead of round the value.

b)      String Functions : The built-in functions of SQL that work on string type of data are called as string functions. String functions in SQL are as follows.

1. ASCII(char) : Converts the specified character to the equivalent ascii code.
2. Char(int) : Converts ascii code to the equivalent character.
3. CharIndex(s1,s2) : returns the starting position where the partial string s1 first occurs in the string s2. Returns zero if s1 does not occur in s2.
4. Lower(S1) : Converts all uppercase letters of the string s1 to lower case letters.
5. Upper(s1) : Converts all lowercase letters of the string s1 to upper case letters.
6. Ltrim(s1) : removes leading blank spaces in the string s1.
7. Rtrim(S1) : Removes trailing blank spaces in the string s1.
8. Reverse(S1) : Reverses the string S1.
9. Right(S1,Len) : returns last Len characters from the string S1.
10. Left(S1,Len) : returns first len characters from the string S1.
11. Soundex(a) : Returns how the given string is pronounced in a four character soundex code.
12. Stuff(s1,a,len,s2) : Replaces the partial string s1 with the partial string s2 starting at position a, replacing len characters of s1.
13. Substring(S1,a,len) : creates a partial string from string s1 starting at the position a with a length of len.

c)       Date Functions : The built-in functions of SQL that work on date type of data are called as date functions and date functions in SQL server are as follows.

1. GetDate() : Returns the current system date and time.
2. DatePart(item,D) : returns the specified part item of a date D as an integer.
3. DateName(item,D) : returns the specified part item of the date D as a string.
4. DateDiff(item,D1,D2) : calculates the difference between the two date parts D1 and D2 and returns the result as an integer in units specified by the value item.
5. DateAdd(item,n,D) : Adds the number n of units specified by the value item to the given date D.

d)      Conversion Functions :  The Built-In functions of SQL Server that are used to convert one type of data to another are called as conversion functions and conversion functions in SQL Server are as follows.

1. Cast(a as type[(length)]) : converts an expression a into the specified data type type.
2. Convert(type[(length}],a) : Equivalent to cast, but the arguments are specified differently.

e)       System Functions : The SQL Server system functions provide extensive information about database objects.

1. Object_ID(objname) : returns the identifier of the database object objname.
2. Object_name(obj_id) : returns the name of the database object with id obj_id.
3. Col_Name(Obj_Id,S_Id) : returns the name of the column belonging to the object obj_id with the identifier s_id.
4. Col_Length(obj,Col) : returns the length of the column belonging to a database object obj.
5. Datalength(s1) : calculates the length of the result of the expression s1.
6. DB_Id(Db_name) : returns the id of the database db_name.
7. DB_Name(Db_id) : returns the name of the database with id Db_id.
8. User_Id(username) : returns the id of specified user name.
9. user_name(uid) : returns the name of the user with id uid.
10. Current_TimeStamp : returns the current date and time.

II.                Ranking functions

1.      Row_number: Displays row numbers

Select row_number() over (order by orderdate desc) as rownum,

Salesorder_id,customer_id,orderdate

From Sales.SalesOrderHeader

Order by OrderDate Desc

Select row_number() over (order by orderdate desc) as rownum,

Salesorder_id,customer_id,orderdate

From Sales.SalesOrderHeader

Where rownum between 101 and 120

Order by OrderDate Desc

Create proc info1 @a int, @b int

As

Select * from (select row_number() over (order by orderdate desc) as rownum,

Salesorderid,customerid,orderdate,from sales.salesorderheader) 0

Where rownum between @a and @a+@b-1

Order by orderdate desc

Other Ranking functions

Select row_number() over (order by city) as rownum,

Rank() over( order by city) as rank,

Dense_Rank() over (order by city as DenseRank,

Ntile(3) over( order by city) as ntile_3,

Ntile(4) over(order by city) as ntile_4,

City, stateprovincecode

From person.address as address

Inner join person.stateprovince as stateprov

On address.stateprovinceid=stateprov.stateprovinceID where countryregioncode=‘US’ and StateprovinceCode=‘AZ’