-------------------------------------------------------
-- MATH FUNCTIONS
-------------------------------------------------------

-- Rounds UP
SELECT CEILING(45.36)

SELECT FLOOR(45.9)

SELECT ROUND(45.36,0), ROUND(45.368,0), ROUND(45.66,0)

SELECT ROUND(45.368,0), ROUND(45.368,1), ROUND(45.368,2), ROUND(45.368,3)

SELECT productname, unitprice, ROUND(unitprice,0) AS Rounded_Price
FROM Products


-- ROUNDS with specified decimal digits (in this case - 0, 1 or -1)
SELECT ROUND(45.36,0), ROUND(45.368,1), ROUND(45.36,-1)

SELECT ROUND(4738,-1),ROUND(4738,-2),ROUND(4738,-3)
-- Random number between 0 and 1
SELECT RAND()

-- Random number between 1 and 100
SELECT CEILING(RAND()*100)

-- Round up all prices
select unitprice, ceiling(UnitPrice)
from products

-- Round new prices
select UnitPrice*0.1 bonus, round(UnitPrice*0.1, 0)"round bonus" 
from products

-------------------------------------------------------
-- CHAR FUNCTIONS
-------------------------------------------------------

SELECT UPPER('HeLlO'), LOWER('HeLlO')

SELECT LastName, UPPER(LastName) "New name"
FROM dbo.Employees

-- Ascii Values
select char(48), char(65), char(66), char(67)

-- Return the location of the first string in the second string
-- (Starting from 1, Returns 0 if not found)

SELECT CHARINDEX('l', 'Hello world')

SELECT CHARINDEX('ll', 'Hello world')

SELECT CHARINDEX('p', 'Hello world')

SELECT LastName, CHARINDEX('a', LastName) "contain a?"
FROM dbo.Employees

-- Return specified number of characters from the LEFT or the RIGHT 
-- of the string.

SELECT LEFT('Hello World',4)

SELECT RIGHT('Hello World', 1)

SELECT employeeid, lastname, RIGHT(lastname, employeeid)
FROM dbo.Employees


SELECT employeeid, lastname, SUBSTRING(lastname, 2, 4)
FROM dbo.Employees


SELECT 'Yekutiel.Marcheshvan@gmail.com',
  CHARINDEX('@', 'Yekutiel.Marcheshvan@gmail.com')


SELECT Substring('Yekutiel.Marcheshvan@gmail.com', 1, CHARINDEX('@', 'Yekutiel.Marcheshvan@gmail.com'))

SELECT LEFT('Yekutiel.Marcheshvan@gmail.com', 
             CHARINDEX('@', 'Yekutiel.Marcheshvan@gmail.com')) + 'Naya-tech.co.il'


SELECT emailaddress,
LEFT(EmailAddress, 
             CHARINDEX('@', EmailAddress)) + 'Naya-tech.co.il'
FROM [AdventureWorks2008R2].[Person].[EmailAddress]



-- Returns the length of a string (number of characters)

SELECT LastName, LEN(LastName) "LENGTH"
FROM dbo.Employees

SELECT LEN('1   2   3')

-- Searches for the second string in the first string, then replaces it
-- with the third string

SELECT REPLACE('Good Morning', 'Good', 'Great')

-- Replaces all occurrences
SELECT REPLACE('Good Morning Good', 'Good', 'Great')

SELECT LastName, REPLACE(LastName, 'A', 'B') "New name"
FROM dbo.Employees

-- Replicate a certain string specified number of times:

SELECT REPLICATE('*',10) "Stars"

-- Reverse a string
SELECT LastName, REVERSE(LastName) "New name"
FROM dbo.Employees

-- Removes Blanks from the right of the string
SELECT RTRIM('abc    ') "New string"

SELECT 'abc    ' + RTRIM('abc    ')+'A'

SELECT 'abc' + LTRIM('   abc    ')+'A'

-- Extract one string from another:
-- Starting from the second parameter, in the length of the Third parameter:

SELECT SUBSTRING('Hello World',3, 3)


-------------------------------------------------------
-- DATE AND TIME FUNCTIONS
-------------------------------------------------------

-- Add or subtract number to add or remove days from a date
SELECT Birthdate, Birthdate + 10
FROM dbo.Employees

-- Get the current date And time
SELECT getdate()

--Get last day of month
SELECT EOMONTH(GETDATE())

-- Add Hours
SELECT getdate() + 1.0/24

SELECT YEAR(Getdate())

SELECT DAY(BirthDate)
FROM Employees

SELECT DATENAME(DW , GetDate())


SELECT DATEPART(QQ , GetDate())



SELECT DAY('2002-03-22') , MONTH('2002-03-22'), YEAR('2002-03-22')


-- DATEADD - Part1: What to add? (days, months, years)
--           Part2: How many to add?
--           Part3: To which date?

SELECT getdate() Curr_date, DATEADD(DAY,3, getdate()) "Add 3 days"

-- Add hours
SELECT getdate() Curr_date, DATEADD(HH,3, getdate()) "Add 3 hours"

-- Add minuets
SELECT getdate() Curr_date, DATEADD(MI,3, getdate()) "Add 3 minuets"

SELECT getdate() Curr_date, DATEADD(yy,3, getdate()) "Add 3 years"

-- Difference between two dates
SELECT DATEDIFF(dd,'20051213',getdate())

SELECT DATEDIFF(mm,'2005-01-01',getdate())

-- Display a certain date element
SELECT DATEPART(mm, getdate())

SELECT DATEPART(yy, getdate())

SELECT DATEPART(day, getdate())

-- Another way to perform the same action
SELECT DAY(getdate()), MONTH(getdate()), YEAR(getdate())

-- In a select:
SELECT DAY(BirthDate), MONTH(BirthDate), YEAR(BirthDate)
FROM dbo.Employees

-- Display a part of a date in a name
SELECT DATENAME(mm, getdate())

SELECT DATENAME(yy, getdate())

SELECT DATENAME(day, getdate())


-----------------------------------------
-- Conversion and Display functions

-- CASTING FUNCTIONS (system functions)

-- Convert a value to a specified format
SELECT CAST(28.764 AS INT) 

SELECT CAST(28.764 AS varchar) 

-- Implicit conversion:
SELECT '74'+28

-- Problem in conversion - tries to convert 'abc' to a number...
SELECT 'abc'+28

-- Solution - Convert 28 to a string:
SELECT 'abc'+CAST(28 AS varchar) 

SELECT Unitprice, CAST(Unitprice AS numeric(5,1)) FROM products

-- Can also user CONVERT. (Cast is more common to use for convertions)
SELECT unitprice, CONVERT(INT, unitprice) FROM products

-- Use CONVERT to display the date to a specific format
-- FORMAT: (the date type has got to be char or varcahr
SELECT GETDATE()

SELECT CONVERT(CHAR(12), GETDATE(), 103)

SELECT CONVERT(CHAR(12), GETDATE(), 101)

SELECT CONVERT(CHAR, GETDATE(), 102)

SELECT CONVERT(CHAR(30), GETDATE(), 109)

-- ISNULL

SELECT CompanyName,Fax 
FROM Suppliers

SELECT CompanyName, ISNULL(Fax,'Unknown') AS Fax
FROM Suppliers 
------------------------
SELECT CustomerID, CompanyName, Region
FROM Customers

SELECT CustomerID, CompanyName, ISNULL(Region, 'No region') Region
FROM dbo.Customers

-- To open SQL Server's help ("Books Online") - Type SHIFT+F1