Hi,
In this blog, I have discussed Sql Server in-built functions to be used in T-sql, queries, DML's. I have detailed them and have provided example of each function. Any suggestion,correction would be highly appriciated.
--Math Functions
--Average function
Select avg(Person_id) avg from db_owner.person
--CharIndex function to find index of a particular alphabet. In example
--CHARTINDEX finds 'a' in first_name column of person starting from 1st character of
--string
Select CHARINDEX('a',First_Name,1)a,first_name from db_owner.Person
Use MS2012;
go
declare @avar xml
RETURN @avar.query('Product/Prices')
truncate table db_owner.person
delete * from db_owner.person where middle_name is null
create table t1(id int identity(1,1) default 1)
select * from db_owner.person
select sum(person_id),count(*) ,first_name from db_owner.person
select top(10)
desc sp_settrigger
declare @var1 nvarchar(20);
select @var1 ='My Example';
Select CharIndex ('m',@var1,1) Col1,
CharIndex('P',@var1,1) COl2,
CharIndex ('x',@var1,1) Col3
GO
---Concat Function
Select ConCat ('Mr.',' Jaidev ',' sharma')
Select Concat(First_name,'--',Last_name) from db_owner.Person
---Left function shows number of character from left side of string
-- in example first four character from left would be displayed
Select Left('jaidev sharma',4)
--Len function to get the total length of a string
Select len('jaidev')
Select len(first_name) leth,First_Name from db_owner.person
---Lowever and upper
Select lower('Jaidev'),upper('jaidev')
Select lower(first_name),upper(Last_Name) from db_owner.person
---Ltrim,Rtrim,trim removes blank spaces
select Ltrim(' jaidev')y,Rtrim('Jaidev ')x
Declare @var1 nvarchar(50);
set @var1 = ' My Name is Jaidev Sharma';
Select Ltrim(@var1)
--Substring to get a portion of a string starting from 2nd parameter and upto
--3rd parameter
Select substring('Jaidev',3,3)
Select First_Name,SubString(First_name,1,len(first_name)) nm from db_owner.person
--Find a particular characher left and right of a string
Select PATINDEX(lower('%S%'),'Australia')
Select First_name from db_owner.person
Select PatIndex('%o%',First_Name),First_Name from db_owner.Person
----Replace find a particular character give in 2nd parameter and replaces it
---a parameter give in 3 parameter. In example in 'jai' string 'a' is replaced with 'u'
---
Select Replace('jai','a','u')
Select First_name,Replace(First_name,'a','h') from db_owner.person
Select count(person_id) emp_count from db_owner.person
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---TSQL Date and Time Data Types and Functions
Select @@DATEFIRST
--to get current_Timestamp
Select CURRENT_TIMESTAMP
--to add date in one particular date. In example 1, I have added DAY,41 (no. of days) in to 16-Jun-2014 to see counting 41 from 16-jun what date
--would fall.
---eg 1
Select DATEADD(day,41,'2014-06-16')
--eg 2 in this I have added Month 1 (one month) in todays date
Select DateAdd(Month,1,SysDateTime())
--eg3 In this I have added Year 1 ( one year) in the today's date.
Select DateAdd(Year,1,SysDateTime())
---DateDiff function tells us the difference between to dates.
Select DateDiff(day,'2014-06-16','2014-07-28') --42 days
Select DateDiff(Month,'2014-06-16','2014-07-28') --1 month
Select DateDiff(Year,'2014-06-16','2014-07-28')
---DATEFROMPARTS
Select DateFromparts(1,2,3)
----DATENAME return a specific information (eg. Year,Month,Day, Day of Year or week) of a date. To get just year portion of date use year,
Select DateName(Year,CURRENT_TIMESTAMP) year
Select DateName(Month,CURRENT_TIMESTAMP) Month
Select DateName(day,Current_TimeStamp) Day
Select DateName(DayOfYear,Current_TimeStamp) Day_Of_Year
Select DateName(Week,Current_TimeStamp) WeekNo
----DATEPART Similar to DateName
Select DatePart(Month,Current_TimeStamp) Month
Select DatePart(Year,Current_Timestamp) Year
Select DatePart(Day,Current_TimeStamp) Day
--DATETIMEFROMPARTS converts integers in Data Format
Select DateTimeFromParts(2014,3,22,13,22,13,14) Dat1
--DATETIME2FROMPARTS
---Select DATETIME2FROMPARTS(2014,3,12,12,03,20,12,1)
SELECT DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 7, 1) as Date_1
--DAY to know current day of the month
Select day(current_timestamp)
--EOMONTH end of month
Select EoMonth(CURRENT_TIMESTAMP)
-- GETDATE()
Select GetDate()
--GETUTCDATE()
Select GETUTCDATE()
---ISDATE checks the format of date. If format is right than it return 1 otherwise 0
Select IsDate('14-03-02')-- Returns 0 as date format is wrong
Select SYSDATETIME()
Select IsDate(CURRENT_TIMESTAMP) --Returns 1 as date format provided by Current_TImeStamp is right
---- MONTH
Select MONTH(current_timestamp)
Select year(CURRENT_TIMESTAMP)
--SmallDateTimeFromParts
Select SMALLDATETIMEFROMPARTS(2014,07,29,1,04)
--SWITCHOFFSET
--- SYSDATETIME returnes system time stamp
Select SysDateTime()
-- SYSDATETIMEOFFSET
-- SYSUTCDATETIME
-- TIMEFROMPARTS
-- TODATETIMEOFFSET
----TSQL System Functions
---
select @@CONNECTIONS,getdate();
--@@Error to knowif error any came
Use Ms2012;
Go
Select * from db_owner.gender;
Update db_owner.Gender set Gender='UnKnown' where Gender_id=3;
delete from db_owner.gender where Gender_id=4;
if @@ERROR <> 0 Print N'There is no Gender Id 4'
Select * from db_owner.gender;
GO
-- @@IDENTITY to know last value IDENTITY value inserted
-- @@ROWCOUNT to see number of rows effected by a T-Sql Statement
use MS2012;
go
Update db_owner.gender set Gender='Male' where Gender_id=1;
Update Db_owner.Gender set gender='Female' Where Gender_id=2;
Update Db_owner.Gender set gender='Unkown' Where Gender_id=3;
if @@ROWCOUNT<> 0
print Concat('The number of rows affected are ' , @@Rowcount);
if @@ROWCOUNT=0
Print Concat(@@ROWCOUNT, ' no row effected' );
Select * from db_owner.gender;
go
-- COALESCE equal to NVL function of Oracle
Alter Table Db_owner.person
alter column middle_name nvarchar(20) null
Select * from db_owner.person
Update db_owner.Person set Middle_name=null where Person_id=103
Select First_name,Coalesce(Middle_name,null,'UnKown') from db_owner.Person
--ERROR_LINE returns the error at particular line when using with Try and Catch block
USE model;
GO
BEGIN TRY
SELECT 8/0;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() AS ErrorLine;
Select ERROR_MESSAGE () As ErrorMessage
Select ERROR_NUMBER () As ErrorNumber
Select ERROR_PROCEDURE() as Proce
Select ERROR_SEVERITY () as Sever
Select ERROR_STATE() as Stat
END CATCH;
GO
/* ERROR_MESSAGE
ERROR_NUMBER
ERROR_PROCEDURE
ERROR_SEVERITY
ERROR_STATE*/
---Host_Id
Select HOST_ID() as HostId
---HOST_NAME
Select Host_name() HostName
---IsNull
Select ISNULL(NUll,'Jai')
Select * from db_owner.person
select IsNull(Middle_name,'Kumar (replaced)'),Middle_name from db_owner.person
---ISNUMERIC either 1 or 0 if 1 than parameter is number
Select ISNUMERIC('a')
--- NULLIF checks if two expressions are same or not. If same than returns
--null otherwise value of first parameter
Select NullIf('a','a') col1,NullIf('a','b'),NullIf('jai','jai')
----Security Functions
--- CURRENT_USER if connect by windows authentication or sa than its show DBO.
Select Current_user as currentUser
Select ORIGINAL_LOGIN() as OrgLogin
--Session user shows the user of sesison. normally it s same as current user function.
Select SESSION_USER
--Shows the user at OS level connected to sql server
Select SYSTEM_USER
--User_Name tell the name of user at sql server level
Select USER_NAME()
TSQL Metadata Functions
--App_Name() gives the name of the applicaiton.In following example it gives 'Microsoft Sql Server Management Stuido - Query' output
Select APP_NAME ()
--DB_ID returns the number assigned to a database
Select DB_ID()
--DB_Name name of database
Select DB_NAME() DBNAMe
---Object_Definition gets the definition of an object
Select * from sys.objects
where name='PERSON'
select * from ms2012.db_owner.gender
Select OBJECT_DEFINITION(object_ID('ms2012.db_owner.gender'))
--Object_id to know the object id
Select Object_id('ms2012.db_owner.person')
--Ojbect Name to know the object name
Select OBJECT_NAME(437576597),Object_id('ms2012.db_owner.person')
Select OBJECT_SCHEMA_NAME(437576597,8)
Select SCHEMA_ID('db_owner')
Select SCHEMA_NAME(16384)
---TSQL Configuration Functions
Select @@LOCK_TIMEOUT as TimeOutOfLog
Select @@MAX_CONNECTIONS as MaxConn
Select @@SERVERNAME as SrvrNm
Select @@SERVICENAME as SrvcNm
select @@SPID as SPID
No comments:
Post a Comment