# Built-in Data Manipulation Functions

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:

```/*
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:

```/*
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;

/******************************
******************************/
/******************************
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:

```/*
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:

```-- Select the first 10 characters of each customer's address

/******************************
SQL Server and MySQL
******************************/
FROM Customers;

/******************************
Oracle
******************************/
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:

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

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