SQL SERVER Function all

--TSQL Aggregate Functions
--AVG Aggregate Function TSQL Tutorial


SELECT AVG(c.amount) AS AverageAmount 
   FROM contracts c 
WHERE c.amount > 400 ;


--COUNT Aggregate Function TSQL Tutorial

SELECT COUNT(c.contract_id) AS CountContracts 
   FROM contracts c 
WHERE c.amount > 400 ;
--MAX Aggregate Function TSQL Tutorial

SELECT MAX(c.amount) AS MaxAmount 
   FROM contracts c 
WHERE c.amount > 300 ;
--MIN Aggregate Function TSQL Tutorial

SELECT MIN(c.amount) AS MinAmount 
   FROM contracts c 
WHERE c.amount > 300 ;
--MIN Aggregate Function TSQL Tutorial

SELECT MIN(c.amount) AS MinAmount 
   FROM contracts c 
WHERE c.amount > 300 ;


--TSQL String Functions
--Charindex TSQL Tutorial


--charindex ( search_expression ,string_expression [ , start_location ] )

USE model; 
GO 
DECLARE @string varchar(64); 

SELECT @string = 'My example'; 
SELECT CHARINDEX('y', @string) as 'Col_1', 
CHARINDEX('x', @string) as 'Col_2', 
CHARINDEX('le', @string,8) as 'Col_3'; 
GO

--Concat Example 1:

SELECT CONCAT ('Daniel', ' - ','Scott') AS FullName;

--Left Example 1:

SELECT LEFT ('abcdefghik',3); 
SELECT LEFT ('12345',2); 

--Len Example 1:

--SELECT LEN ('abcde'); 
--Result: 5
--SELECT LEN ('0012345fff'); 
--Result: 10

--LOWER Example:

DECLARE @mystring varchar(250); 
SET @mystring = 'Convert TRANSACT SQL LOWER Function'; 
SELECT LOWER(@mystring);

--LTRIM Example:

DECLARE @mystring varchar(250); 
SET @mystring = '      Test LTRIM function - delete spaces from the beginning of my string.'; 
SELECT LTRIM(@mystring);  

--Substring Example:

SELECT Name, SUBSTRING(name, 1, 1) AS Initial, SUBSTRING(name, 1, 3) AS Short_name 
FROM states;

--Patindex TSQL Tutorial

USE model; 
GO 
SELECT PATINDEX('%u%',country) as 'Example' 
FROM dbo.students; 
GO

--Replace function

SELECT REPLACE('abcdefghijk','def','yyy'); 

----Right Example 1:

SELECT RIGHT ('zxcvbnm',3); 
Result: bnm
SELECT RIGHT ('12345',2); 
Result: 45

--RTRIM Example:

DECLARE @mystring varchar(250); 
SET @mystring = 'Test RTRIM function - delete spaces from the beginning of my string.       '; 
SELECT RTRIM(@mystring); 


--UPPER Example:

DECLARE @mystring varchar(250); 
SET @mystring = 'Convert transact sql UPPER Function'; 
SELECT UPPER(@mystring);


---TSQL Date and Time Data Types and Functions


--@@Datefirst Example

SELECT @@SERVERNAME AS 'Server Name', 
@@DATEFIRST AS 'First Day';  

--CURRENT_TIMESTAMP Example:

SELECT 'The current timestamp is: '+ CONVERT(char(25), CURRENT_TIMESTAMP) as System_date;

--DATEADD Example:

DECLARE @date datetime = '2014-01-01 13:10:10'; 
SELECT 'add_year' as Add_year, DATEADD(year,2,@date) as Result;

--DATEDIFF Example:

DECLARE @start_date datetime = '2007-02-19 22:24:10'; 
DECLARE @end_date datetime = '2014-02-19 22:24:10'; 
SELECT DATEDIFF(year, @start_date, @end_date) as Result;
SELECT DATEDIFF(year, '2013-02-19 21:21:59', '2014-02-19 21:21:59') as Year_diff, 
DATEDIFF(month, '2013-02-19 21:21:59', '2014-02-19 21:21:59') as Months_diff, 
DATEDIFF(day, '2013-02-19 21:21:59', '2014-02-19 21:21:59') as Days_diff;

--DATEFROMPARTS Example:

SELECT DATEFROMPARTS ( 2014, 02, 22 ) AS Date;

--DATENAME Example:

SELECT DATENAME(year,'2014-02-22 08:23:49.1234567 +05:10') as Year, 
DATENAME(month,'2014-02-22 08:23:49.1234567 +05:10') as Month, 
DATENAME(day,'2014-02-22 08:23:49.1234567 +05:10') as Day, 
DATENAME(dayofyear,'2014-02-22 08:23:49.1234567 +05:10') as Dayofyear, 
DATENAME(week,'2014-02-22 08:23:49.1234567 +05:10') as Week, 
DATENAME(weekday,'2014-02-22 08:23:49.1234567 +05:10') as Weekday, 
DATENAME(quarter,'2014-02-22 08:23:49.1234567 +05:10') as Quarter;

--DATEPART Example:

SELECT DATEPART(year,'2014-02-22 08:23:49.1234567 +05:10') as Year, 
DATEPART(month,'2014-02-22 08:23:49.1234567 +05:10') as Month, 
DATEPART(day,'2014-02-22 08:23:49.1234567 +05:10') as Day, 
DATEPART(dayofyear,'2014-02-22 08:23:49.1234567 +05:10') as Dayofyear, 
DATEPART(week,'2014-02-22 08:23:49.1234567 +05:10') as Week, 
DATEPART(weekday,'2014-02-22 08:23:49.1234567 +05:10') as Weekday, 
DATEPART(quarter,'2014-02-22 08:23:49.1234567 +05:10') as Quarter;


--DATETIME2FROMPARTS Example:
--DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fractions, precision)
SELECT DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 7, 1) as Date_1, 
DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 77, 2) as Date_2, 
DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 777, 3) as Date_3, 
DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 7777, 4) as Date_4;
--DAY :
SELECT DAY('2014-02-22') as Day_1, 
DAY('2014-02-22 08:23:49.1234567 +07:10') as Day_2, 
DAY(SYSDATETIME()) as Day_3; 


--EOMONTH Example:

DECLARE @lastday DATETIME = '02/22/2014'; 
SELECT EOMONTH ( GETDATE() ) AS Result;

--GETDATE() Example:

select getdate() as System_date;

--GETUTCDATE() Example:
--This value represents the current UTC time: Coordinated Universal Time.
select getutcdate() as System_date;

--ISDATE Example:

SELECT ISDATE('01/28/2014') as Valid_date, 
ISDATE('21/21/2014') as Invalid_date;

--MONTH Example:

SELECT MONTH('2014-03-01') as Month_1, 
MONTH('2014-03-01 08:38:49.1234567 +07:10') as Month_2, 
MONTH(SYSDATETIME()) as Month_3;

--SMALLDATETIMEFROMPARTS Example:

SELECT SMALLDATETIMEFROMPARTS ( 2014, 03, 01, 15, 50 ) AS Result;

--SWITCHOFFSET Example:

select switchoffset (CONVERT(datetimeoffset, GETDATE()), '-03:00') AS Result_1, 
switchoffset (CONVERT(datetimeoffset, GETDATE()), '+03:00') AS Result_2;

--SYSDATETIME Example:

SELECT CONVERT (date, SYSDATETIME()) as Date, 
CONVERT (time, SYSDATETIME()) as Time, 
SYSDATETIME() as SysDateTime;

--SYSDATETIMEOFFSET Example:

SELECT CONVERT (date, SYSDATETIMEOFFSET()) as Date, 
CONVERT (time, SYSDATETIMEOFFSET()) as Time, 
SYSDATETIMEOFFSET() as SysDateTimeOffSet;

--SYSUTCDATETIME Example:

SELECT CONVERT (date, SYSUTCDATETIME()) as Date, 
CONVERT (time, SYSUTCDATETIME()) as Time, 
SYSDATETIME() as SysDateTime, 
SYSUTCDATETIME() as SysUtcDateTime;

--TIMEFROMPARTS Example:

SELECT TIMEFROMPARTS ( 16, 47, 56, 7, 1 ) as Time_1, 
TIMEFROMPARTS ( 14, 47, 56, 70, 2 ) as Time_2, 
TIMEFROMPARTS ( 14, 47, 56, 700, 3 ) as Time_3;
--TODATETIMEOFFSET
SELECT TODATETIMEOFFSET (GETDATE(), '-01:00') Result_1, 
TODATETIMEOFFSET (SYSDATETIME(), -60) Result_2;

--YEAR Example:

SELECT YEAR('2014-03-01') as Result_1, 
YEAR('2014-03-01 17:01:25.1234567 +07:10') as Result_2, 
YEAR(SYSDATETIME()) as Result_3;

--TSQL System Functions

--@@Connections Example

SELECT GETDATE() AS 'Login date', 
@@CONNECTIONS AS 'Login id'; 

--@@Error Example

USE model; 
GO 
UPDATE products 
   SET product_type = 'DEDE' 
   WHERE product_id = 2; 
IF @@ERROR <> 0 
   PRINT N'Error: Product type.'; 
GO 

--@@IDENTITY
USE model; 
GO 
insert into students(id,first_name, last_name, gender,city, country) 
values(7,'Ashley','THOMPSON','F','Liverpool','England'); 
GO 
SELECT @@IDENTITY AS 'Identity';
--@@Rowcount Example
UPDATE students 
SET section = N'History' 
WHERE id = 7 
IF @@ROWCOUNT > 0 
PRINT 'Updated'  

--COALESCE
select COALESCE(null,11) as Value;


--Error_line() Example

USE model; 
GO 
BEGIN TRY 
   SELECT 8/0; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_LINE() AS Error_Line; 
END CATCH; 
GO



--Error_message() Example

USE model; 
GO 
BEGIN TRY 
   SELECT -5*9/0; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_MESSAGE() AS Error_Message; 
END CATCH; 
GO

--Error_number() Example

USE model; 
GO 
BEGIN TRY 
   SELECT 18/0; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_NUMBER() AS Error_Number; 
END CATCH; 


--Error_procedure() Example

CREATE PROCEDURE test_procedure 
AS 
   SELECT 3/0; 
GO 


USE model; 
GO 
BEGIN TRY 
   EXECUTE test_procedure; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_PROCEDURE() AS Error_Procedure; 
END CATCH; 
GO

--Error_severity() Example
--Return the code number of the error that use the CATCH block of a TRY ... CATCH
USE model; 
GO 
BEGIN TRY 
   SELECT 26/0; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_SEVERITY() AS Error_Severity; 
END CATCH; 
GO


--Error_state() Example

USE model; 
GO 
BEGIN TRY 
   SELECT 16/0; 
END TRY 
BEGIN CATCH 
   SELECT ERROR_STATE() AS Error_State; 
END CATCH; 
GO

--HOST_ID() Example:

select HOST_ID() as Host_number;
--HOST_NAME() Example:

select HOST_NAME() as Host_name;

--ISNULL Example:

SELECT ISNULL('abc',28) as col_1, 
ISNULL('01/28/2014',7) as col_2, 
ISNULL(null,9) as col_3, 
ISNULL(null,null) as col_4, 
ISNULL('-',-1) as col_5;

--ISNUMERIC Example:

SELECT ISNUMERIC('abc') as col_1, 
ISNUMERIC('01/28/2014') as col_2, 
ISNUMERIC(123) as col_3, 
ISNUMERIC(null) as col_4, 
ISNUMERIC(-1) as col_5, 
ISNUMERIC(0.8) as col_6;

--NULLIF Example:

select NULLIF ( 'aaa' , 'aaa' ) as col_1, 
NULLIF ( 'bbb' , '3' ) as col_2, 
NULLIF ( 'ccc' , null ) as col_3;

--TSQL Security Functions

--CURRENT_USER Example:

select CURRENT_USER as Username;


--ORIGINAL_LOGIN() Example:

select ORIGINAL_LOGIN() as Original_user;

--SESSION_USER Example:

select SESSION_USER as 'Session_user';

--SYSTEM_USER Example:

select SYSTEM_USER as 'System_user';

--SYSTEM_USER Example:

select SYSTEM_USER as 'System_user';

---TSQL Metadata Functions


--APP_NAME() Example:

select APP_NAME() as Name;

--DB_ID() Example:

SELECT DB_ID() AS Database_1, 
DB_ID(N'master') AS Database_2;

--DB_NAME() Example:

SELECT DB_NAME() AS 'Database name', 
DB_NAME (1) AS 'Database name by id';

--OBJECT_DEFINITION Example:

SELECT OBJECT_DEFINITION(OBJECT_ID('master.dbo.products_list')) AS 'Object definition of my view';

--OBJECT_ID() Example:

SELECT OBJECT_ID('model.dbo.products') AS 'Object ID of Table', 
OBJECT_ID('master.dbo.products_list') AS 'Object ID of View';

--OBJECT_NAME() Example:

SELECT OBJECT_NAME(279672044) AS 'Object name of View', 
OBJECT_NAME(245575913,2) AS 'Object name of Table';


--OBJECT_SCHEMA_NAME Example:

SELECT OBJECT_SCHEMA_NAME(279672044) AS 'Schema object name of View', 
OBJECT_SCHEMA_NAME(245575913,2) AS 'Schema object name of Table';
--SCHEMA_ID() Example:

SELECT SCHEMA_ID() AS 'Schema id', 
SCHEMA_ID('dbo') AS 'Schema id by name';
--SCHEMA_NAME() Example:

SELECT SCHEMA_NAME() AS 'Schema name', 
SCHEMA_NAME(1) AS 'Schema name by id';

--TSQL Configuration Functions

--@@LOCK_TIMEOUT Example

SELECT @@LOCK_TIMEOUT AS Lock_Timeout;

--@@Max_connections Example

SELECT @@SERVERNAME AS 'Server Name', 
@@MAX_CONNECTIONS AS 'Max Connections'; 
--@@SERVERNAME Example

SELECT @@SERVERNAME AS 'Local Server Name';

--@@SERVICENAME Example

SELECT @@SERVICENAME AS 'Instance Service Name';

--@@SPID Example

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login_Name', USER AS 'User_Name';