some sql function

1) Launch SQL Server Management Studio and connect to the local instance of SQL Server.
I will start by showing you simple examples on how to use the AGGREGATE built-in  functions.COUNT() function, counts the number of rows in the aggregate query. COUNTBIG() is more or less the same as COUNT() but returns a bigint data type. SUM() functions adds column values and AVG() averages column values. MIN() and MAX() functions return the lowest or maximum column value respectively. Let’s have a look at some examples.

USE Northwind
go

SELECT COUNT(*) FROM dbo.Employees

SELECT COUNT(*) AS NUMEmps,COUNT(region) AS NumRegions FROM dbo.Employees

SELECT SUM(UnitPrice) FROM dbo.[Order Details]
SELECT AVG(UnitPrice) FROM dbo.[Order Details]
SELECT MIN(UnitPrice) FROM dbo.[Order Details]
SELECT MAX(UnitPrice) FROM dbo.[Order Details]

I will provide you with a simple example on how to use the ISNUMERIC() function. In this example I am checking whether the values in the PostalCode column of the Customers table are numeric or not.

USE Northwind
go

SELECT PostalCode,ISNUMERIC(PostalCode)
FROM dbo.customers

Run the query and see the results.
We can generate random numbers with T-SQL by using the RAND() function.This function generates random numbers between 0 and 1.When specifying a seed inside the RAND(678) function then all the values that are returned from SQL Server are the same no matter how many times you execute the statement.

SELECT RAND(),RAND(),RAND(),RAND()
SELECT RAND(),RAND(),RAND(),RAND(),RAND(678)

Run the query and see the results.
Another very important function is the ROUND() function. We can specify the  precision in this function. That means if we want to round to the decimal value, whole (integer) value. We do that by setting the second argument in the arguments list inside the parenthesis. If we specify a number other that 0 for the third argument in the parenthesis in the arguments list it means we want to truncate the value.
ROUND ( numeric_expression , length [ ,function ] )

SELECT  UnitPrice ,
ROUND(UnitPrice, 0) ,
ROUND(UnitPrice, 0, 1) ,
ROUND(UnitPrice, 1) ,
ROUND(UnitPrice, -1)
FROM    dbo.Products

Run the query and see the results.
The next function I am going to look is the ABS() function that returns the absolute (positive) value of the specified numeric expression.

SELECT ABS(-2),ABS(-3),ABS(9)

Run the query and see the results.
The next function I am going to look is the SQRT() function that returns the square root of the specified value.

SELECT SQRT(16),SQRT(100)

Run the query and see the results.
The next function I am going to look is the SQUARE() function that Returns the square of the specified value.

SELECT SQUARE(16),SQUARE(100)

Run the query and see the results.
Now let’s move on and talk about the STRING built-in T-SQL functions. I will start with the REPLACE() function. It is very simple really. We do specify the expression we want to be searched (usually a column) then what to search for and finally the third argument is what we want to replace it with. In this example we replace the ‘Ms.” with ‘Miss’ in the column TitleOfCourtesy of the table Employees

USE Northwind
GO

SELECT TitleOfCourtesy,
REPLACE(TitleOfCourtesy, 'Ms.', 'Miss')
FROM    [dbo].[Employees]

Run the query and see the results.
Another built-in string function is the STUFF() function. It inserts a string into another string and we can specify the start position and the length .In this example we
replace (starting from the second character) the ‘Dr.’ with  ‘Doctor’

SELECT TitleOfCourtesy,
STUFF(TitleOfCourtesy, 2,5, 'octor')
FROM    [dbo].[Employees]
WHERE TitleOfCourtesy='Dr.'

Run the query and see the results.
If we want to find the number of characters in a string we can use the LEN() function

USE Northwind
GO

SELECT LEN(City)

FROM .[dbo].[Employees]

Run the query and see the results.
We can use the LEFT() and RIGHT() functions to get a portion of a column or expression ( left or right ) by specifying a number of characters.Have a look at the examples below.

USE Northwind
GO

SELECT LEFT(Phone,5)
FROM dbo.[Shippers]

SELECT LEFT(Phone,LEN(phone)-8)
FROM dbo.[Shippers]

SELECT RIGHT(Phone,4)
FROM dbo.[Shippers]

SELECT RIGHT(Phone,LEN(phone)-10)
FROM dbo.[Shippers]

Run the query and see the results. You can see that I am using LEN() as well. The real power of functions is showing when we use them together.
Now I want to explain how to use the SUBSTRING()function. This function allows us to return only a part of a column or expression by specifying the start position (second argument) and the number of characters we want to get back (third argument)

USE Northwind
GO

SELECT  SUBSTRING(FirstName, 1, 1) + '. ' + LastName
FROM  [dbo].[Employees]

Run the query and see the results.
Now I want to explain how to use the CHARINDEX() function.We provide a   ‘-’ (expression ) and we look where this “-” appears in the HomePhone column and we return the position of that “-” inside the HomePhone column.In the second example I use the CHARINDEX() and the SUBSTRING() functions to get only the areacode of the HomePhone column

SELECT  CHARINDEX('-', HomePhone)
FROM    dbo.Employees

SELECT  HomePhone ,
SUBSTRING(HomePhone, 1, ( CHARINDEX(')', HomePhone) )) AS 'AreaCode'
FROM    dbo.Employees

Run the query and see the results.There is another function called PATINDEX() . It returns the  starting position of the first occurrence of a pattern in a specified expression.
We can use all the wildcards we use in LIKE statements like “%” or “_”

SELECT ProductName,QuantityPerUnit,PATINDEX('24 - % g pkgs.',QuantityPerUnit)
FROM dbo.Products
SELECT [CategoryName],PATINDEX('%MEAT%',categoryname)

FROM [dbo].[Categories]

Run the query and see the results. In this example we will get (in the third column) the starting position of the pattern ‘24 – % g pkgs.‘ in the QuantityPerUnit column of the Products table.The second example we look for the starting position of the pattern ‘%meat%‘ in the CategoryName column of the Categories table.
If there is no match it will return 0, as CHARINDEX() does.
The next function I am going to look is SPACE().It returns a string of repeated spaces.

USE Northwind

GO

SELECT  FirstName + SPACE(3) + LastName
FROM    dbo.Employees

Run the query and see the results.
Now I am going to look into CHAR() and ASCII() functions. The CHAR() converts an int ASCII code to a character.ASCII() Returns the ASCII code value of the character.

USE Northwind
GO

SELECT CHAR(78) + CHAR(13) + CHAR(80)

SELECT ASCII('P')

Run the query and see the results.
Now I am going to use the UPPER(),LOWER(),LTRIM(),RTRIM() functions. The first function returns an expression that is lowercase and converts it to uppercase. The second function does the exact opposite.There is no TRIM function in SQL Server. The LTRIM() and RTRIM() built in functions remove leading spaces from the left or right of the character expression.

USE Northwind

GO

SELECT  UPPER(FirstName) + ' ' + LOWER(LastName)
FROM    dbo.Employees

SELECT LTRIM ('    I like T-SQL')

SELECT RTRIM ('I like T-SQL      ')

Now let’s have a look at the built in functions that support the date and time data types.The GETDATE() function returns the date and the time of the server that the query is executing. The YEAR(),MONTH(),DAY() functions return the year,month and day of the expression or column expression that we pass as an argument.

SELECT GETDATE()

SELECT MONTH(GETDATE())
SELECT YEAR(GETDATE())
SELECT DAY(GETDATE())

Run the T-SQL statements and see the results.
We can use the very useful DATEPART() and DATENAME() functions. The DATEPART() function returns an integer that represents the specified datepart of the specified date. The datepart is something we define as the first argument. Have a look here , so you can see the various options of the datepart argument.The DATEPART() function Returns a character string that represents the specified datepart of the specified date.The datepart is something we define as the first argument. Have a look here , so you can see the various options of the datepart argument

SELECT
DATEPART(dy, GETDATE()) AS DayOfYear,
DATEPART(dd, GETDATE()) AS DayNum,
DATEPART(ww, GETDATE()) AS WeekNum,
DATEPART(dw, GETDATE()) AS Weekday,
DATEPART(hh, GETDATE()) AS Hour,
DATEPART(mi, GETDATE()) AS Minute,
DATEPART(ss, GETDATE()) AS Seconds;

SELECT
DATENAME(qq, GETDATE()) AS Quarter,
DATENAME(mm, GETDATE()) AS Month,
DATENAME(dw, GETDATE()) AS Weekday,
DATENAME(hh, GETDATE()) AS Hour,
DATENAME(mi, GETDATE()) AS Minute,
DATENAME(ss, GETDATE()) AS Seconds;

Run the query and see the results.I am going to look into DATEADD() and DATEDIFF() functions.The DATEADD() function is useful when we want to add/subtract  a specified datepart to a date.  The DATEDIFF() function is useful when you want to calculate the difference or the timespan between two dates. In the first example below I add months , years and days to the current date. Then in the next example I get the difference in days between the OrderDate, the RequiredDate and the ShippedDate columns of the table Orders.In the last example I get the last day of the month.

USE Northwind
GO

SELECT
DATEADD(yy, 3, GETDATE()) AS AddedYears,
DATEADD(mm, 6, GETDATE()) AS AddedMonths,
DATEADD(dd, 6, GETDATE()) AS AddedDays;

SELECT
OrderDate, RequiredDate, ShippedDate,
DATEDIFF(dd, OrderDate, RequiredDate) AS LeadTime,
DATEDIFF(dd, OrderDate, ShippedDate) AS DaysToShip,
DATEDIFF(dd, ShippedDate, RequiredDate) AS DaysEarly
FROM dbo.Orders;

-- finally I get the last day of the month

DECLARE @date datetime
SET @date='2011-10-28'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)),
DATEADD(m,1,@date)) AS thelastdayofthemonth

Run the query and see the results.

USE Northwind
GO

SELECT
DATEADD(yy, 3, GETDATE()) AS AddedYears,
DATEADD(mm, 6, GETDATE()) AS AddedMonths,
DATEADD(dd, 6, GETDATE()) AS AddedDays;

SELECT
OrderDate, RequiredDate, ShippedDate,
DATEDIFF(dd, OrderDate, RequiredDate) AS LeadTime,
DATEDIFF(dd, OrderDate, ShippedDate) AS DaysToShip,
DATEDIFF(dd, ShippedDate, RequiredDate) AS DaysEarly
FROM dbo.Orders;

-- finally I get the last day of the month

DECLARE @date datetime
SET @date='2011-10-28'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)),
DATEADD(m,1,@date)) AS thelastdayofthemonth

Run the query and see the results.
Very often when we write T-SQL statements we want to convert from one data type to another. Sometimes that happens implicitly because SQL Server is clever enough to make the conversion. We should not rely on that fact and always try to convert data types explicitly. Some data types are not compatible with each other and we cannot apply conversion.We will use the CAST() and CONVERT() functions. Let’s look at some examples. I have some comments. The second example (SELECT CAST(‘Robert’ AS INT)) will fail…

USE Northwind
GO

SELECT CAST('12345' AS INT) -- success
SELECT CAST('Robert' AS INT) --fail
SELECT CAST('12/12/1977' AS DATETIME) -- success

--Decimal to Integer
SELECT CAST(69.95 AS INT) -- success

--Decimal to String
SELECT CAST(69.95 AS CHAR(10)) -- success

--Using CONVERT with style

SELECT GETDATE();
SELECT CONVERT(varchar(20), GETDATE(), 1);
SELECT CONVERT(varchar(20), GETDATE(), 101);
SELECT CONVERT(varchar(20), GETDATE(), 102);
SELECT CONVERT(varchar(20), GETDATE(), 126);

SELECT
Quantity,
'$' + CONVERT(varchar(12), Unitprice, 1) AS Unitprice,
'$' + CONVERT(varchar(12), Quantity * UnitPrice, 1) AS Amount
FROM [Order Details]

Run the queries and see the results.Now I would like to talk about some global functions. Some people call them global variables. The  first one is @@ROWCOUNT that returns the number of rows that are affected by the last T-SQL statement.

USE Northwind
GO

SELECT CompanyName
FROM dbo.Customers
WHERE Country = 'Germany'
SELECT @@ROWCOUNT AS RowsAffected;

Run the query and see the results.
The  second one is @@TRANCOUNT() . This is useful when working with explicit transactions and we want to check if there are any transactions pending.That means if we have transactions that we either need to rollback or commit.The “BEGIN TRANSACTION” statement increments the transaction counter by one (@@TRANCOUNT() =1) and the “ROLLBACK TRANSACTION” sets the  (@@TRANCOUNT() =0). @@IDENTITY retrieves the newly added value inserted in a column that has Identity set.

USE Northwind
GO
BEGIN TRANSACTION
INSERT INTO dbo.Categories (CategoryName)
VALUES ('My Category');
SELECT @@IDENTITY AS AddedCategoryID;
 SELECT @@TRANCOUNT AS TRANCOUNT;

ROLLBACK TRANSACTION
SELECT @@TRANCOUNT AS TRANCOUNT;

Run the query and see the results.When an error occurs within a T-SQL statement and we want to capture it, we can use the @@ERROR that returns the error number for the last Transact-SQL statement executed.In the following statement there is an error in the update statement. After that I get the number of the error(515 in this case) in the select statement.

USE Northwind
GO
UPDATE dbo.Region
SET RegionDescription = NULL
WHERE RegionID=1;
SELECT @@ERROR AS Error;

Run the query and see the results. I will show in another post how to use the TRY CATCH statements in T-SQL for exception handling.
Now I would like to demonstrate with few examples the use of the Ranking functions.Τhe first one is ROW_NUMBER() which returns the sequential number of the row within a result set.The second one is RANK() function and it returns the rank of each row within a result set.The third one is DENSERANK() function and it returns the rank of each row within a result set without any gaps in the ranking.The fourth one is NTILE() function and it distributes the rows in an ordered partition into a specified number of groups.Basically is like saying that I have 1000 values and I want to distribute them into 10 groups. NTILE returns the number of the group to which the row belongs.
ProductName                                               UnitPrice           RANK        DENSERANK
Schoggi Schokolade                                                  43.90                       11                     11
Vegie-spread                                                             43.90                       11                     11
Northwoods Cranberry Sauce                                 40.00                       1 3                   12
Alice Mutton                                                              39.00                       14                    13
For example when we run the T-SQL below we get some results like the ones below. Note the difference in the results for RANK and DENSERANK functions.

USE Northwind
GO

SELECT ProductName, UnitPrice,
ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS RowNumber

FROM dbo.Products
ORDER BY UnitPrice DESC;

SELECT ProductName, UnitPrice,
RANK() OVER (ORDER BY UnitPrice DESC) AS Rank
FROM dbo.Products
ORDER BY UnitPrice DESC;

SELECT ProductName, UnitPrice,

DENSE_RANK() OVER (ORDER BY UnitPrice DESC) AS DenseRank,

FROM dbo.Products
ORDER BY UnitPrice DESC;

SELECT ProductName, UnitPrice,
NTILE(10) OVER (ORDER BY UnitPrice DESC) AS NTILE 10
FROM dbo.Products
ORDER BY UnitPrice DESC;

Run the query and see the results.I have an example for each of the ranking functions.
Now I would like to talk about some functions that have to do with NULL values.The ISNULL() function replaces NULL with the specified replacement value.Another useful function when working with NULL is the NULLIF() function that returns a null value if the two specified expressions are equal . NULL values are not part of aggregate operations.For example if we want to find the average of the Units in stock for our products we would like to exclude the products with 0 units in our inventory.So we would like to have those 0 values convereted to NULL. Have a look at the second example below.
The other function I would like to talk is COALESCE() function which helps us to find the first nonnull expression among its arguments.Have a look at the third example where we want to find the first value that is non null in the Region and Postal columns of the Suppliers table.. If none of the values are null the first value is returned.

USE Northwind
go

SELECT CompanyName, City, ISNULL(Region,'Not Known') AS Region
FROM dbo.Suppliers

SELECT AVG(NULLIF(UnitsInStock, 0)) FROM dbo.Products;
-- instead of --
SELECT AVG(UnitsInStock) FROM dbo.Products;

SELECT CompanyName, Region, PostalCode,
COALESCE(Region, PostalCode) AS 'Region-PostalCode'
FROM dbo.Suppliers;