------------------------- -- 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