-------------------------
-- Scalar Functions
-------------------------
------------------------
-- Numeric Functions
------------------------
-- * Ceiling
SELECT productName , unitPrice , unitPrice * 0.3 , CEILING(unitPrice * 0.3)
FROM products
-- * Floor
SELECT productName , unitPrice , unitPrice * 0.3 , FLOOR(unitPrice * 0.3)
FROM products
-- * Round
-- * 0-4 down
-- * 5-9 up
SELECT ROUND(154.89 , -2)
SELECT productName , unitPrice , unitPrice * 0.3 , ROUND(unitPrice * 0.3 , 1 )
FROM products
SELECT ROUND(RAND() * 10 , 1)
--------------------------------
-- String Functions
--------------------------------
SELECT productName, UPPER(productName) , LOWER(productName)
FROM products
SELECT productName , LEN(productName)
FROM products
SELECT CHARINDEX('l' , 'hello world')
SELECT productName , CHARINDEX ('a' , productName)
FROM products /*might be usefull for substring*/
SELECT productName , SUBSTRING (productName , 1 , 3)
FROM products
SELECT lastName , firstName ,
LOWER(SUBSTRING(firstName , 1 ,2) + SUBSTRING(lastName , 1 , 4) + '@gmail.com') AS 'Email'
FROM employees
SELECT productName , SUBSTRING (productName , CHARINDEX ('a' , productName) , 3)
FROM products
SELECT lastName , RIGHT(lastName , 3) , LEFT(lastName , 3)
FROM employees
SELECT REPLACE('hello world' , 'hello' , 'goodbye cruel' )
SELECT productName , REPLACE(productName , 'a' , '$' )
FROM products
SELECT REPLICATE('*' , 10)
SELECT REVERSE (productName )
FROM products
SELECT LEN('sometext ')
SELECT RTRIM('text ')
SELECT LTRIM(' text')
SELECT LTRIM(RTRIM(' test '))
--------------------------------
-- Date Functions
--------------------------------
SELECT GETDATE()
SELECT YEAR(GETDATE())
SELECT lastName , hireDate , YEAR(hireDate) , MONTH(hireDate) , DAY(hireDate)
FROM employees
WHERE MONTH(hireDate) = 8
SELECT lastName , hireDate , YEAR(hireDate) , MONTH(hireDate) , DAY(hireDate) ,
DATENAME(month , hireDate) , DATENAME(dw , hireDate)
FROM employees
SELECT lastName , hireDate , DATEPART(YEAR , hireDate) , DATEPART( MONTH , hireDate) , DATEPART(DAY, hireDate) ,
DATEPART(QQ , hireDate) ,
DATENAME(month , hireDate) , DATENAME(dw , hireDate)
FROM employees
SELECT lastName , hireDate , DATEADD(month , 3 , hiredate)
FROM employees
SELECT lastName , birthDate , DATEDIFF(year , birthDate , getdate())
FROM employees
-----------------------------------
-- Conversion Functions
-----------------------------------
SELECT lastName , hireDate , lastName + ' -- ' + CAST(hireDate AS varchar)
FROM employees
SELECT lastName , hireDate , lastName + ' -- ' + CONVERT(varchar , hireDate)
FROM employees
SELECT lastName , hireDate , lastName + ' -- ' + CONVERT(varchar , hireDate , 113)
FROM employees
SELECT lastName , hireDate , lastName + ' -- ' + CONVERT(varchar , hireDate , 107)
FROM employees
-----------------------------
-- Null Functions
-----------------------------
SELECT lastName , region , ISNULL(region , 'No Region')
FROM employees
SELECT productName , unitPrice , ISNULL(unitPrice , 0) * 3
FROM products
----------------------------
-- CASE
----------------------------
SELECT productName , unitPrice ,
CASE WHEN unitPrice BETWEEN 0 AND 50 THEN 'Low Price'
WHEN unitPrice BETWEEN 51 AND 60 then 'Medium Price'
WHEN unitPrice BETWEEN 61 AND 70 then 'High Price'
ELSE 'Other Price'
END AS 'PriceGrades'
FROM products
