Built-in Data Manipulation Functions

Contact Us or call 1-877-932-8228
Built-in Data Manipulation Functions

Built-in Data Manipulation Functions

In this section, we will discuss some of the more common built-in data manipulation functions. Unfortunately, the functions differ greatly between databases, so you should be sure to check your database documentation when using these functions.

The tables below show some of the more common math, string, and date functions.

Common Math Functions

Common Math Functions
Description SQL Server Oracle MySQL
Absolute value ABS ABS ABS
Smallest integer >= value CEILING CEIL CEILING
Round down to nearest integer FLOOR FLOOR FLOOR
Power POWER POWER POWER
Round ROUND ROUND ROUND
Square root SQRT SQRT SQRT
Formatting numbers to two decimal places CAST(num AS decimal(8,2)) CAST(num AS decimal(8,2)) FORMAT(num,2) or CAST(num AS decimal(8,2))

Code Sample:

AdvancedSelects/Demos/Functions-Math1.sql
/*
Select freight as is and
freight rounded to the first decimal (e.g, 1.150 becomes 1.200)
from the Orders tables
*/

SELECT Freight, ROUND(Freight,1)  AS ApproxFreight
FROM Orders;

The above SELECT statement will return the following results (not all rows shown):

Code Sample:

AdvancedSelects/Demos/Functions-Math2.sql
/*
Select the unit price as is and
unit price as a CHAR(10)
from the Products tables
*/
SELECT UnitPrice, CAST(UnitPrice AS CHAR(10))
FROM Products;

/******************************
ADD CONCATENATION
******************************/
/******************************
SQL Server
******************************/
SELECT UnitPrice, '$' + CAST(UnitPrice AS CHAR(10))
FROM Products;

/******************************
Oracle
******************************/
SELECT UnitPrice, '$' || CAST(UnitPrice AS CHAR(10))
FROM Products;

/******************************
MySQL
******************************/
SELECT UnitPrice, CONCAT('$',CAST(UnitPrice AS CHAR(10)))
FROM Products;

The above SELECT statement will return the following results (not all rows shown):

Note that the CHAR(10) creates space for 10 characters and if the unit price required more than 10 characters you would need to increase it accordingly.

Common String Functions

Common String Functions
Description SQL Server Oracle MySQL
Convert characters to lowercase LOWER LOWER LOWER
Convert characters to uppercase UPPER UPPER UPPER
Remove trailing blank spaces RTRIM RTRIM RTRIM
Remove leading blank spaces LTRIM LTRIM LTRIM
Returns part of a string SUBSTRING SUBSTR SUBSTRING

Code Sample:

AdvancedSelects/Demos/Functions-String1.sql
/*
Select first and last name from employees in all uppercase letters
*/
SELECT UPPER(FirstName), UPPER(LastName)
FROM Employees;

The above SELECT statement will return the following results:

Code Sample:

AdvancedSelects/Demos/Functions-String2.sql
-- Select the first 10 characters of each customer's address

/******************************
SQL Server and MySQL
******************************/
SELECT SUBSTRING(Address,1,10)
FROM Customers;

/******************************
Oracle
******************************/
SELECT SUBSTR(Address,1,10)
FROM Customers;

The above SELECT statement will return the following results (not all rows shown):

Common Date Functions

Common Date Functions
Description SQL Server Oracle MySQL
Date addition DATEADD (use +) DATE_ADD
Date subtraction DATEDIFF (use -) DATEDIFF
Convert date to string DATENAME TO_CHAR DATE_FORMAT
Convert date to number DATEPART TO_NUMBER(TO_CHAR) EXTRACT
Get current date and time GETDATE SYSDATE NOW

Code Sample:

AdvancedSelects/Demos/Functions-Date1.sql
-- Find the hiring age of each employee

/******************************
SQL Server
******************************/
SELECT LastName, BirthDate, HireDate, DATEDIFF(year,BirthDate,HireDate) AS HireAge
FROM Employees
ORDER BY HireAge;

/******************************
Oracle
******************************/
SELECT  LastName, BirthDate, HireDate, FLOOR((HireDate - BirthDate)/365.25) AS HireAge
FROM Employees
ORDER BY HireAge;

/******************************
MySQL
******************************/
-- Find the hiring age of each employee
-- in versions of MySQL prior to 4.1.1
SELECT LastName, BirthDate, HireDate, YEAR(HireDate)-YEAR(BirthDate) AS HireAge
FROM Employees;

-- In MySQL 4.1.1 and later, DATEDIFF() returns the number of days between
-- two dates. You can then divide and floor to get age.
SELECT LastName, BirthDate, HireDate, FLOOR(DATEDIFF(HireDate,BirthDate)/365) AS HireAge
FROM Employees
ORDER BY HireAge;

The above SELECT statement will return the following results in SQL Server:

And like this in Oracle:

Note for SQL Server users: SQL Server is subtracting the year the employee was born from the year (s)he was hired. This does not give us an accurate age. We'll fix this in an upcoming exercise.

Code Sample:

AdvancedSelects/Demos/Functions-Date2.sql
-- Find the Birth month for every employee

/******************************
SQL Server
******************************/
SELECT FirstName, LastName, DATENAME(month,BirthDate) AS BirthMonth
FROM Employees
ORDER BY DATEPART(month,BirthDate);

/******************************
Oracle
******************************/
SELECT FirstName, LastName, TO_CHAR(BirthDate,'MONTH') AS BirthMonth
FROM Employees
ORDER BY TO_NUMBER(TO_CHAR(BirthDate,'MM'));

/******************************
MySQL
******************************/
SELECT FirstName, LastName, DATE_FORMAT(BirthDate, '%M') AS BirthMonth
FROM Employees
ORDER BY EXTRACT(MONTH FROM BirthDate);

The above SELECT statement will return the following results:

Next