SQL all type of functions

string functions  (23 functions)
---------------------------------------------------------------

select upper('prasad')

select lower('PRASAD')

select ltrim('   prasad')

select rtrim('prasad    ')

select substring('prasad',1,3)

select replace('prasad','p','si')

select reverse('prasad')

select len('prasad')

select ascii('p')

select char(78)

select charindex('prasad',97)

select difference(5,8)

select left('prasad',1)

select right('prasad',2)

select len('prasad')

select nchar(102)

select patindex('prasad','s')

select quotename('PRASAD')

select replicate('prasad',5)

select empname from tblemp where soundex(empname)=soundex('prasad')

select space(5)

select str('20.4')

select str('20.6')

select stuff('prasad',r,s,d)

select substring('prasad',4,6)

select unicode('prasad')

----------------------------------------------------------------------
datetime functions
-----------------------------------------------------------------------
select getdate()

select day(getdate())

select dateadd(yy,5,getdate())

select dateadd(dd,5,getdate())

select dateadd(mm,5,getdate())

select datename(dw,getdate())

select datename(dy,getdate())

select datename(mm,getdate())

select datename(dd,getdate())

select datediff(dd,2011-06-10,getdate())

select datediff(dd,getdate(),2011-06-10)

select datediff(mm,2011-06-10,getdate())

select datediff(yy,2008,getdate())

select datepart(dd,getdate())

select datepart(mm,getdate())

select datepart(yy,getdate())

select getutcdate()

select month(2005-05-06)

select year(getdate())

-------------------------------------------------------------------
agrigate functions
-------------------------------------------------------------------

select avg(5895/565)

select binary_checksum(1001001000)

select checksum(256456+455454)

select checksum_agg(54545)

select count(4544)

select count_big(544)

select grouping(salary) from tblemp

select max(salary) from tblemp

select min(salary) from tblemp

select stdev(salary) from tblemp

select stdevp(salary) from tblemp

select sum(salary) from tblemp

select var(salary) from tblemp

select varp(salary) from tblemp

-----------------------------------------------------------------------
Configuration Functions
------------------------------------------------------------------------

select @@datefirst

select @@Dbts

select @@langid

select @@language

select @@lock_timeout

select @@max_connections

select @@max_precision

select @@nestlevel

select @@options

select @@remserver

select @@servername

select @@servicename

select @@spid

select @@textsize

select @@version

----------------------------------------------------------------------------------
Cursor Functions
----------------------------------------------------------------------------------

select @@cursor_rows

select cursor_status()

select @@fetch_status

----------------------------------------------------------------------------------
mathematical functions
-----------------------------------------------------------------------------------
select abs(536.4)

select acos(.665)

select asin(.4545)

select atan(4545.556)

select atn2(4545.556,445.554)

select ceiling(2454.445)

select cos(0)

select cot(120)

select degrees(45)

select exp(5+5)

select floor(556.1)

select log(10)

select log10(20)

select pi()

select power(2,5)

select radians(55)

select rand()

select round(55,6)

select sign(90)

select sqrt(2)

select square(2)

select tan(90)

-------------------------------------------------------------------------------------
MetaData Functions
-------------------------------------------------------------------------------------
select col_length('empname','empid') from tblemp

select col_name(4,4) from tblemp






insert into tblemp values('ram','40000','ccc','ccc')

select * from tblemp

select max(salary) from tblemp

select * from tblemp where salary=(select max(salary) from tblemp)

select * from tblemp where salary=(select max(salary) from tblemp)

using distinct key word find max or min salary
select min(salary) from (select distinct top 3 salary from tblpp order by salary desc)tblpp
another type
select min(salary) from tblpp where salary in (select distinct top 3 salary from tblpp order by salary desc)
another way
select max(salary) from tblpp where salary =(select max(salary) from tblpp where salary <(select max(salary) from tblpp))



using alter command add column to table
alter table tblpp add address nvarchar(50)
alter table tblpp add salary nvarchar(50)
sp_renamedb olddbname,new dbname

select * from tblemp distinct where top 2 salary=(select * from tblemp order by salalry desc)  //error

select * from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp)) //2nd higest salary

select * from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp))))

select * from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp))

create view emp1
as
select empid,empname,salary from tblemp


exec dbo.udf_tblemp


var s=from n in dc.tblMasterUsers join o in dc.tblOrganizations on n.UserID equals 0.UserID select new{n.UserID,n.FirstName,o.OrgID,o.Orgname};