Aggregate :It can apply on group of data
Ex: Sum(row1,r2,...)
Sum(ColumnName)
Aggregate will expect collection data as input
It will rerun single value as Output.
Ex: Sum, Max, MIn, Avg, Count and Count_Big
If you add any Aggregate function and any addtional columns in select statement, then you have to add group by
Ex: Select EName, Sum(LeaveDays) from Emp
Group By Ename
If you don't add Group By Ename then you will get error.
Select COUNT(*) from Emp --For small tables
Select COUNT_BIG(*) from EMP --Counts big data. Ex: if you have a table with more data like 50Cr
Scalar: Single Valued
Ex: UPPER('')
Scalar will expect single value as input
it will rerun based on number of rows.
Scalar functions
Ex:
Numeric
Date
String
System
MetaData
Date:
GetDate() --Current Server date and time
DateAdd(<Units>,<Number>,<DateValue>)
Ex:
Select DateAdd(d,10,GETDATE())
Select DateAdd(Month,10,GETDATE())
Select DateAdd(Year,-10,GETDATE())
DateDiff(<Units>,<FromDate>,<EndDate>)
EX:
Select DATEDIFF(d,<FromDate>,<EndDate>) as Diff from Table
Select DATEDIFF(Month,<FromDate>,<EndDate>) as Diff from Table
DatePart(<Units>,<Date>)
Ex:
Select DATEPART(month,getdate())
Select DATEPART(Year,getdate())
DateName(<Units>,<Date>)
Numeric Functions
--ABS(s)
:Absulute Value
Select ABS(-5)
O/p:5
--CEAILING(n)
It will return next higest value of integer
Select CEAILING(2.1)
O/P=3
Select CEAILING(-2.1)
O/P=-2
-EXP(n)
Ex: EXP(10)
It will give e power 10
--FLOOR(n)
It will return next lowest value of integer
--LOG(n)
It's base is e
--LOG10(n)
It's Base is 10
--PI()
--POwer(x,y)
--RAND()
It will return random value from 0 to 1
--ROUND(n,p)
ROUND(22.33,1) o/p: 22.3
ROUND(22.33,-1) o/p:20
--ROWCOUNT_BIG
Last executed query records count(Effected rows)
--select SQRT(9)
String Functions
----------------
--CHAR(Integer)
Select CHAR
--ASCII (Character)
Select ASCII('A')
Select ASCII('x')
--CHARINDEX(String1,String2)
Select CHARINDEX('n','India')
Select CHARINDEX('d','redmond')
if(CHARINDEX('red','redmond')>0)
Print 'Redmond'
else
Print 'Hi'
--LEFT(String,length)
Ex:
Select LEFT('India',3)
O/P: Ind
Select LEFT('India',5)
O/P: India
Select LEFT('India',8)
O/p: India
Select LEFT('India',0)
O/P:
--RIGHT(String,length)
Select RIGHT('India',3)
O/P: dia
--LEN(string)
Select LEN('India')
O/P: 5
Ex: Select LEN(Ename) from Emp
--LOWER(string)
select LOWER('INDIA')
O/P: india
--UPPER(string)
--LTRIM(String)
Select LTRIM(' India ')
O/p:'India '
--RTRIM(String)
Select RTRIM(' India ')
O/P:' India'
Select LTRIM(RTRIM(' India '))
O/P: 'India'
--REPLACE(string1,string2,string3)
Search for string2 in string1 and replace with string3
Ex: Select REPLACE('Banguloor','Ban','Man')
O/P: Manguloor
--REPLICATE(string,int)
select replicate('A',100)
--REVERSE(string)
Select REVERSE(Ename) from EMP
--STR(float,len,d)
Convert floating values to string
Ex: select STR(2.345,3)
--STUFF(str1,starting Charector,How Many chrectors,str2)
Select STUFF('Hyderabad IT place',10,0,' is Nice ')
O/p: Hyderabad Nice IT place
--SUBSTRING(str,Start,len)
Ex: select substring('Hyderabad IT place',7,3)
o/p:bad
