# Built in Functions

Published Sep 3, 2024
Contribute to Docs

`Built-in functions` are predefined functions that can be used to perform operations on data at any time. They are usually categorized according to the data types (strings, date, numeric, and other built-in functions) that they operate on, and are used to simplify complex calculations and data manipulations by reusing code blocks for common tasks.

## String Functions

String functions allow operations to be performed on string data.

### Concat Function

`CONCAT()`: Concatenates two or more strings.

``````CONCAT(string1, string2, ...)
``````

### Length Function

`LENGTH()`: Returns the length of a string.

``````LENGTH(string)
``````

### Upper Function

`UPPER()`: Converts a string to upper-case.

``````UPPER(string)
``````

## Numeric Functions

Numeric functions allow to perform operations on numeric data.

### Abs Function

`ABS()`: Returns the absolute value of a number.

``````ABS(value)
``````

### Ceil Function

`CEIL()`: Returns the smallest integer value that is greater than or equal to a number.

``````CEIL(value)
``````

### Round function

`ROUND()`: Rounds a number to a specific number of decimal places.

``````ROUND(number, decimals)
``````

## Date and Time Functions

Date and Time functions allow to perform operations on date and time data.

### Now function

`NOW()`: Returns the current date and time.

``````NOW()
``````

`DATE_ADD()`: Adds a time interval to a date.

``````DATE_ADD(date, INTERVAL value unit)
``````

### DATEDIFF Function

`DATEDIFF()`: Returns the number of days between two dates.

``````DATEDIFF(date1, date2)
``````

## Aggregate Functions

Aggregate functions allow to perform calculations on a set of values and return a single value.

### SUM Function

`SUM()`: Returns the sum of a set of values.

``````SUM(expression)
``````

### AVG Function

`AVG()`: Returns the average value of a set of values.

``````AVG(expression)
``````

### COUNT Function

`COUNT()`: Returns the number of rows in a table.

``````COUNT(expression)
``````

## Control Flow Functions

Control Flow functions return different values based on different conditions.

### IF Function

`IF()`: Returns a value if a condition is true, and another value if it is false.

``````IF(condition, true_value, false_value)
``````

### CASE Expression

CASE is a statement that goes through conditions and returns a value when the first condition is met and closes with an END clause. Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.

``````CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END
``````

## JSON Functions

JSON functions are used to manipulate JSON data.

### JSON_EXTRACT Function

`JSON_EXTRACT()`: Extracts data from a JSON document.

More about JSON can be learned here

``````JSON_EXTRACT(json_doc, path)
``````

### JSON_ARRAY Function

`JSON_ARRAY()`: Creates a JSON array from a list of values.

``````JSON_ARRAY(value1, value2, ...)
``````

## Encryption and Compression Functions

These functions provide encryption and compression utilities.

### AES_ENCRYPT Function

`AES_ENCRYPT()`: Encrypts a string using AES (Advanced Encryption Standard) encryption, and returns the original (binary) in hexadecimal format.

``````AES_ENCRYPT(str, key_str)
``````

### MD5 Function

`MD5()`: Calculates the MD5 (message-digest algorithm) hash of a string. This means that the value is returned as a string of 32 hexadecimal digits, or NULL if the argument was NULL.

``````MD5(string)
``````

## Conclusion

These are some examples of primary built-in functions in MySQL across various categories, illustrating its usage with examples. Each function serves a specific purpose which allows data manipulation and querying in MySQL more efficiently and powerfully.