System Functions

/* 1. System Function -- Built in function like
        a.String Functions
        b.Mathamatical Functions
        c.Date Functions
       
2. User Defined Functions
    User can create the function as per the customer requirnment-Reusable functions.*/
--------------------------------------------------------------------------------------
--STRING FUNCTION:-
------------------
 SUBSTRING
 LOWER
 UPPER
 CHARINDEX
 REVERSE
 LEN
 REPLACE
 ASCII

SELECT    SUBSTRING(EmpId,4,3)
FROM    TblEmpDetails(nolock)

SELECT    UPPER(EmpName)
FROM    dbo.TblEmpDetails(nolock)

SELECT    LOWER(EmpName)
FROM    dbo.TblEmpDetails(nolock)

SELECT    CHARINDEX(EmpName,'EMP','EMP')
FROM    dbo.TblEmpDetails(nolock)

SELECT    REVERSE(EmpName)
FROM    dbo.TblEmpDetails(nolock)

SELECT    LEN(EmpName)
FROM    dbo.TblEmpDetails(nolock)

SELECT    LEN('Mahadevan')

SELECT REPLACE(EmpId,'EMP','EMPLOYEE')
FROM    dbo.TblEmpDetails(nolock)

SELECT REPLACE(EmpId,'10','TEN')
FROM    dbo.TblEmpDetails(nolock)

SELECT ASCII('B')
from dbo.TblEmpDetails

------------------------------------------------------
DATE FUNCTION:-
---------------
1.DATEPART
2.DATEADD
3.CONVERT
4.DATEDIFF


SELECT *
FROM    dbo.TblEmpDetails(nolock)

SELECT    DATEPART(DAY,DOJ)
FROM    dbo.TblEmpDetails(nolock)

SELECT    DATEPART(MONTH,DOJ)
FROM    dbo.TblEmpDetails(nolock)

SELECT    DATEPART(YEAR,DOJ)
FROM    dbo.TblEmpDetails(nolock)

SELECT    DATEADD(DAY,5,DOJ)
FROM    dbo.TblEmpDetails(nolock)

SELECT    DATEADD(MONTH,5,DOJ)
FROM    dbo.TblEmpDetails(nolock)

SELECT    DATEADD(YEAR,5,DOJ)
FROM    dbo.TblEmpDetails(nolock)

SELECT DATEDIFF(DAY,'01/01/2008','01/01/2009')
SELECT DATEDIFF(MONTH,'01/01/2008','01/01/2009')
SELECT DATEDIFF(YEAR,'01/01/2008','01/01/2009')

CONVERT -- Convert into Date Format

Format - 101 to 120

SELECT    CONVERT(VARCHAR(15),DOJ,101)
FROM    DBO.TblEmpDetails(nolock)

SELECT    CONVERT(VARCHAR(15),DOJ,102)
FROM    DBO.TblEmpDetails(nolock)

SELECT    CONVERT(VARCHAR(15),DOJ,103)
FROM    DBO.TblEmpDetails(nolock)

SELECT    CONVERT(VARCHAR(15),DOJ,104)
FROM    DBO.TblEmpDetails(nolock)

SELECT    CONVERT(VARCHAR(15),DOJ,105)
FROM    DBO.TblEmpDetails(nolock)

SELECT    CONVERT(VARCHAR(15),DOJ,106)
FROM    DBO.TblEmpDetails(nolock)

SELECT    CONVERT(VARCHAR(15),DOJ,107)
FROM    DBO.TblEmpDetails(nolock)

SELECT    CONVERT(VARCHAR(15),DOJ,108)
FROM    DBO.TblEmpDetails(nolock)

SELECT    CONVERT(VARCHAR(20),DOJ,109)
FROM    DBO.TblEmpDetails(nolock)

SELECT    CONVERT(VARCHAR(20),DOJ,110)
FROM    DBO.TblEmpDetails(nolock)

SELECT    CONVERT(VARCHAR(20),DOJ,111)
FROM    DBO.TblEmpDetails(nolock)

SELECT DAY(DOJ),MONTH(DOJ),YEAR(DOJ)
FROM    dbo.TblEmpDetails(nolock)

-----------------------------------------------------------------------
MATHAMATICAL FUNCTION:-
-----------------------
SIN
COS
TAN
ATAN
ASIN
ACOS
LOG
SQRT
POWER


SELECT SIN(10)

SELECT COS(10)

SELECT TAN(90)

SELECT COT(10)

SELECT LOG(10)

SELECT    SQRT(100)

SELECT    POWER(10,3)
-----------------------------------------------------------------------


alter    procedure FecthTblEmp
as
begin

select    EmpId,EmpName
from    dbo.TblEmpDetails(nolock)





end
go

exec FecthTblEmp

sp_helptext FecthTblEmp


CREATE    procedure FecthTblEmp
as
begin

select    EmpId,EmpName
from    dbo.TblEmpDetails(nolock)

end