Basic Functions (String, Date, Math)

Difficulty: Basic
Generated on: 2025-07-10 02:23:05
Category: SQL Cheatsheet for Database Development

SQL Cheatsheet: Basic Functions (String, Date, Math)

This cheatsheet provides a quick reference for commonly used SQL functions related to string manipulation, date/time operations, and mathematical calculations. It’s designed to be practical and developer-friendly.

1. Quick Overview

Function Type	Description	When to Use
String	Functions that manipulate text data, such as extracting substrings, changing case, or concatenating strings.	When you need to clean, format, or analyze text data stored in your database. Examples: Standardizing names, parsing addresses, searching for patterns.
Date	Functions that deal with date and time values, such as extracting date parts, adding/subtracting intervals.	When you need to perform date-based calculations, such as calculating age, determining the day of the week, or filtering data based on date ranges.
Math	Functions that perform mathematical calculations, such as rounding numbers, calculating averages, or finding maximum/minimum values.	When you need to perform numerical analysis or calculations on data stored in your database. Examples: Calculating sales totals, determining average order values, finding the highest salary.

2. Syntax

String Functions

Function	Syntax	Description
`UPPER()`	`UPPER(string)`	Converts a string to uppercase.
`LOWER()`	`LOWER(string)`	Converts a string to lowercase.
`LENGTH()`	`LENGTH(string)` (MySQL, PostgreSQL), `LEN(string)` (SQL Server), `LENGTHB(string)` (Oracle)	Returns the length of a string (in characters). Note the database-specific variation.
`SUBSTRING()`	`SUBSTRING(string, start, length)` (MySQL, PostgreSQL, SQL Server), `SUBSTR(string, start, length)` (Oracle)	Extracts a substring from a string, starting at a specified position and with a specified length. Oracle uses `SUBSTR`.
`TRIM()`	`TRIM(string)`	Removes leading and trailing whitespace from a string.
`LTRIM()`	`LTRIM(string)`	Removes leading whitespace from a string.
`RTRIM()`	`RTRIM(string)`	Removes trailing whitespace from a string.
`REPLACE()`	`REPLACE(string, old_substring, new_substring)`	Replaces all occurrences of a substring with another substring.
`CONCAT()`	`CONCAT(string1, string2, ...)` (MySQL, PostgreSQL), `string1 + string2` (SQL Server), `string1
`LIKE`	`column LIKE pattern`	Performs pattern matching using wildcards (`%` for zero or more characters, `_` for a single character).
`POSITION()`	`POSITION(substring IN string)` (PostgreSQL), `CHARINDEX(substring, string)` (SQL Server), `INSTR(string, substring)` (Oracle), `LOCATE(substring, string)` (MySQL)	Returns the position of a substring within a string. Database implementations vary.
`INITCAP()`	`INITCAP(string)` (Oracle, PostgreSQL)	Converts the first letter of each word in a string to uppercase and the rest to lowercase. Not available in MySQL or SQL Server natively (requires user-defined function).

Date Functions

Function	Syntax	Description
`NOW()`	`NOW()` (MySQL, PostgreSQL)	Returns the current date and time. In SQL Server: `GETDATE()`, Oracle: `SYSDATE` or `SYSTIMESTAMP`
`CURDATE()`	`CURDATE()` (MySQL), `CURRENT_DATE` (PostgreSQL, SQL Server)	Returns the current date. Oracle: `TRUNC(SYSDATE)` (truncates time part)
`CURTIME()`	`CURTIME()` (MySQL), `CURRENT_TIME` (PostgreSQL, SQL Server)	Returns the current time. Oracle: `TO_CHAR(SYSDATE, 'HH24:MI:SS')`
`DATE()`	`DATE(datetime)` (MySQL, PostgreSQL, SQL Server)	Extracts the date part from a datetime value. In Oracle, you can use `TRUNC(datetime)` for the same result.
`EXTRACT()`	`EXTRACT(part FROM date)` (PostgreSQL, Oracle)	Extracts a specific part of a date/time (e.g., year, month, day). In SQL Server, use `DATEPART()` or `YEAR()`, `MONTH()`, `DAY()`. MySQL has `YEAR()`, `MONTH()`, `DAY()`, etc. functions.
`DATE_ADD()`	`DATE_ADD(date, INTERVAL value unit)` (MySQL), `date + INTERVAL 'value unit'` (PostgreSQL), `DATEADD(unit, value, date)` (SQL Server), `date + INTERVAL 'value' unit` (Oracle)	Adds a specified interval to a date. Database implementations vary significantly. See examples below.
`DATE_SUB()`	`DATE_SUB(date, INTERVAL value unit)` (MySQL), `date - INTERVAL 'value unit'` (PostgreSQL), `DATEADD(unit, -value, date)` (SQL Server), `date - INTERVAL 'value' unit` (Oracle)	Subtracts a specified interval from a date. Database implementations vary significantly. See examples below.
`DATEDIFF()`	`DATEDIFF(date1, date2)` (MySQL), `date1 - date2` (PostgreSQL), `DATEDIFF(unit, date2, date1)` (SQL Server), `date1 - date2` (Oracle)	Calculates the difference between two dates. The units of difference vary by database. In PostgreSQL/Oracle, the result is in days. In SQL Server, you specify the unit. MySQL returns days.
`DAYNAME()`	`DAYNAME(date)` (MySQL), `TO_CHAR(date, 'Day')` (PostgreSQL, Oracle), `DATENAME(dw, date)` (SQL Server)	Returns the name of the day of the week. Database implementations vary.
`MONTHNAME()`	`MONTHNAME(date)` (MySQL), `TO_CHAR(date, 'Month')` (PostgreSQL, Oracle), `DATENAME(month, date)` (SQL Server)	Returns the name of the month. Database implementations vary.

Math Functions

Function	Syntax	Description
`ROUND()`	`ROUND(number, decimals)`	Rounds a number to a specified number of decimal places.
`FLOOR()`	`FLOOR(number)`	Returns the largest integer less than or equal to a number.
`CEILING()`	`CEILING(number)` (MySQL, PostgreSQL), `CEIL(number)` (SQL Server, Oracle)	Returns the smallest integer greater than or equal to a number. Note the variation in spelling.
`ABS()`	`ABS(number)`	Returns the absolute value of a number.
`MOD()`	`MOD(number, divisor)`	Returns the remainder of a division operation. Some databases use the `%` operator (e.g., `number % divisor`).
`RAND()`	`RAND()` (MySQL, SQL Server), `RANDOM()` (PostgreSQL), `DBMS_RANDOM.VALUE` (Oracle)	Returns a random number between 0 and 1. Oracle requires `DBMS_RANDOM.VALUE`. PostgreSQL returns a value between 0 and 1. MySQL returns a value between 0 and 1. SQL Server returns a value between 0 and 1.
`SQRT()`	`SQRT(number)`	Returns the square root of a number.
`POWER()`	`POWER(number, exponent)`	Returns the value of a number raised to a specified power.
`TRUNCATE()`	`TRUNCATE(number, decimals)` (MySQL)	Truncates a number to a specified number of decimal places without rounding. SQL Server: `ROUND(number, decimals, 1)`, Oracle: `TRUNC(number, decimals)`, PostgreSQL: `TRUNC(number::numeric, decimals)`

3. Common Use Cases

String Functions

Data Cleaning: Standardizing names (e.g., UPPER(TRIM(name))), removing unwanted characters, correcting inconsistencies.
Data Formatting: Formatting phone numbers, zip codes, or other data fields.
Data Analysis: Extracting parts of a string for analysis (e.g., extracting the domain name from an email address).
Search & Filtering: Using LIKE to find records matching a specific pattern.
Concatenation: Creating full names from first and last names.

Date Functions

Age Calculation: Calculating the age of a customer based on their birthdate.
Date Range Filtering: Selecting records within a specific date range.
Reporting: Grouping data by month, quarter, or year.
Scheduling: Determining the next upcoming date for a recurring event.
Time Series Analysis: Analyzing trends over time.

Math Functions

Calculating Totals: Summing up values in a column to get a total.
Averaging Data: Calculating the average of a set of values.
Statistical Analysis: Performing basic statistical calculations (e.g., standard deviation, variance).
Financial Calculations: Calculating interest rates, loan payments, etc.
Data Rounding: Rounding numbers to a specific number of decimal places for reporting.

4. Best Practices

String Functions

Use COLLATE for Case-Insensitive Comparisons: WHERE column LIKE 'value%' COLLATE Latin1_General_CI_AI (SQL Server Example). Check your database’s collation options.
Index on Frequently Searched Columns: Create indexes on columns used in LIKE clauses for faster searches.
Avoid Excessive String Concatenation: Concatenating large strings can be performance-intensive. Consider alternative approaches if possible.
Be Mindful of Character Encoding: Ensure consistent character encoding throughout your database to avoid unexpected results.
Use TRIM() to Remove Whitespace: Always trim input data to ensure consistency and prevent errors in comparisons.
Security: When using user input in LIKE clauses, sanitize the input to prevent SQL injection attacks (e.g., escape wildcard characters).

Date Functions

Use Appropriate Data Types: Store dates and times in dedicated date/time data types, not as strings.
Use Standard Date Formats: Use consistent date formats to avoid ambiguity (e.g., YYYY-MM-DD).
Index on Frequently Filtered Date Columns: Create indexes on date columns used in WHERE clauses for faster filtering.
Be Aware of Time Zones: Handle time zones carefully, especially when dealing with data from multiple locations. Store all times in UTC in the database, and convert to local time zones upon retrieval.
Avoid Calculations on Date Columns: Whenever possible, pre-calculate date values or use indexed views/materialized views to improve performance.

Math Functions

Choose the Right Data Type: Use appropriate numeric data types (e.g., INT, DECIMAL, FLOAT) to avoid data loss or overflow errors.
Be Aware of Precision: Understand the limitations of floating-point numbers and use DECIMAL for precise calculations (e.g., financial calculations).
Use Indexes for Aggregation: Create indexes on columns used in aggregate functions (e.g., SUM(), AVG()) to improve performance.
Avoid Division by Zero: Use CASE statements or other techniques to prevent division by zero errors.

5. Examples

Sample Data

-- Create a sample table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary) VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '2022-01-15', 60000.00),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '2021-05-20', 75000.00),
(3, 'Peter', 'Jones', 'peter.jones@example.com', '2023-03-10', 55000.00),
(4, 'Alice', 'Brown', 'alice.brown@example.com', '2022-08-01', 80000.00),
(5, 'Bob', 'Wilson', 'bob.wilson@example.com', '2023-06-25', 65000.00);

String Function Examples

-- 1. Concatenate first and last names
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; -- MySQL, PostgreSQL
-- SQL Server: SELECT first_name + ' ' + last_name AS full_name FROM employees;
-- Oracle: SELECT first_name || ' ' || last_name AS full_name FROM employees;

-- 2. Convert email addresses to lowercase
SELECT LOWER(email) AS lowercase_email FROM employees;

-- 3. Extract the domain name from an email address (MySQL)
SELECT SUBSTRING(email, POSITION('@' IN email) + 1) AS domain FROM employees;
-- PostgreSQL: SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1);
-- SQL Server: SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email));
-- Oracle: SELECT SUBSTR(email, INSTR(email, '@') + 1) FROM employees;

-- 4. Find employees whose last name starts with 'S'
SELECT * FROM employees WHERE last_name LIKE 'S%';

-- 5. Remove leading and trailing spaces from first names
SELECT TRIM(first_name) FROM employees;

-- 6. Replace .com with .net in email addresses
SELECT REPLACE(email, '.com', '.net') FROM employees;

-- 7. Length of first name
SELECT LENGTH(first_name) FROM employees; -- MySQL, PostgreSQL
-- SQL Server: SELECT LEN(first_name) FROM employees;
-- Oracle: SELECT LENGTHB(first_name) FROM employees;

--8. Get the first three characters of the last name
SELECT SUBSTRING(last_name, 1, 3) FROM employees; -- MySQL, PostgreSQL, SQL Server
-- Oracle: SELECT SUBSTR(last_name, 1, 3) FROM employees;

-- 9. Capitalize the first letter of each word in the full name (Oracle, PostgreSQL)
-- Not available in MySQL or SQL Server natively.
-- SELECT INITCAP(first_name || ' ' || last_name) AS formatted_name FROM employees; --Oracle
-- SELECT INITCAP(first_name || ' ' || last_name) AS formatted_name FROM employees; --PostgreSQL

Date Function Examples

-- 1. Get the current date and time
SELECT NOW(); -- MySQL, PostgreSQL
-- SQL Server: SELECT GETDATE();
-- Oracle: SELECT SYSDATE FROM dual; -- need dual table

-- 2. Get the current date
SELECT CURDATE(); -- MySQL
SELECT CURRENT_DATE; -- PostgreSQL, SQL Server
-- Oracle: SELECT TRUNC(SYSDATE) FROM dual;

-- 3. Extract the year from the hire date
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees; -- PostgreSQL, Oracle
-- SQL Server: SELECT YEAR(hire_date) AS hire_year FROM employees;
-- MySQL: SELECT YEAR(hire_date) AS hire_year FROM employees;

-- 4. Calculate the number of days between the hire date and today (MySQL)
SELECT DATEDIFF(CURDATE(), hire_date) AS days_since_hire FROM employees;
-- PostgreSQL: SELECT CURRENT_DATE - hire_date AS days_since_hire FROM employees;
-- SQL Server: SELECT DATEDIFF(day, hire_date, GETDATE()) AS days_since_hire FROM employees;
-- Oracle: SELECT SYSDATE - hire_date AS days_since_hire FROM employees; -- returns number of days.

-- 5. Add 30 days to the hire date (MySQL)
SELECT DATE_ADD(hire_date, INTERVAL 30 DAY) AS new_date FROM employees;
-- PostgreSQL: SELECT hire_date + INTERVAL '30 days' AS new_date FROM employees;
-- SQL Server: SELECT DATEADD(day, 30, hire_date) AS new_date FROM employees;
-- Oracle: SELECT hire_date + INTERVAL '30' DAY AS new_date FROM employees;

-- 6. Get the day of the week for the hire date (MySQL)
SELECT DAYNAME(hire_date) AS hire_day FROM employees;
-- PostgreSQL: SELECT TO_CHAR(hire_date, 'Day') AS hire_day FROM employees;
-- SQL Server: SELECT DATENAME(dw, hire_date) AS hire_day FROM employees;
-- Oracle: SELECT TO_CHAR(hire_date, 'Day') AS hire_day FROM employees;

-- 7. Get the month name for the hire date (MySQL)
SELECT MONTHNAME(hire_date) FROM employees;
-- PostgreSQL: SELECT TO_CHAR(hire_date, 'Month') FROM employees;
-- SQL Server: SELECT DATENAME(month, hire_date) FROM employees;
-- Oracle: SELECT TO_CHAR(hire_date, 'Month') FROM employees;

Math Function Examples

-- 1. Round the salary to the nearest hundred
SELECT ROUND(salary, -2) AS rounded_salary FROM employees;

-- 2. Calculate the absolute value of the salary difference from 70000
SELECT ABS(salary - 70000) AS salary_difference FROM employees;

-- 3. Calculate the remainder of the salary divided by 1000 (MySQL, PostgreSQL)
SELECT MOD(salary, 1000) AS salary_remainder FROM employees;
-- SQL Server: SELECT salary % 1000 AS salary_remainder FROM employees;
-- Oracle: SELECT MOD(salary, 1000) AS salary_remainder FROM employees FROM dual;

-- 4. Get the ceiling of the salary (MySQL, PostgreSQL)
SELECT CEILING(salary) FROM employees;

--5. Get the ceiling of the salary (SQL Server, Oracle)
SELECT CEIL(salary) FROM employees;

-- 6. Increase salary by 5%
SELECT employee_id, salary, ROUND(salary * 1.05, 2) AS increased_salary FROM employees;

-- 7. Get a random number
SELECT RAND(); -- MySQL, SQL Server
SELECT RANDOM(); -- PostgreSQL
-- Oracle: SELECT DBMS_RANDOM.VALUE FROM dual;

6. Common Pitfalls

String Functions

Case Sensitivity: Remember that string comparisons are often case-sensitive. Use LOWER() or UPPER() for case-insensitive comparisons.
Null Values: String functions may return unexpected results when dealing with NULL values. Use IS NULL or IS NOT NULL to handle NULL values explicitly.
Wildcard Characters: Be careful when using wildcard characters (% and _) in LIKE clauses. Escape these characters if you want to search for them literally.
Different Database Implementations: Be aware of the database-specific syntax for string functions.

Date Functions

Date Formats: Incorrect date formats can lead to errors. Use consistent date formats and the appropriate functions to convert between formats.
Time Zones: Ignoring time zones can lead to incorrect results, especially when dealing with data from multiple locations.
Date Arithmetic: Date arithmetic can be tricky due to leap years and varying month lengths. Use the appropriate functions to add or subtract intervals.
Database-Specific Date Handling: Date handling varies significantly between databases. Always consult the documentation for your specific database.

Math Functions

Data Type Overflow: Performing calculations that result in values outside the range of the data type can lead to overflow errors.
Division by Zero: Always check for division by zero before performing division operations.
Floating-Point Precision: Be aware of the limitations of floating-point numbers and use DECIMAL for precise calculations.
Null Values: Math functions generally return NULL when one of the arguments is NULL.

7. Database Variations

This table summarizes the main differences between the databases covered in this cheatsheet. Always refer to the official documentation for the most accurate and up-to-date information.

Feature	MySQL	PostgreSQL	SQL Server	Oracle
String Length	`LENGTH(string)`	`LENGTH(string)`	`LEN(string)`	`LENGTHB(string)`
Substring	`SUBSTRING(string, start, length)`	`SUBSTRING(string, start, length)`	`SUBSTRING(string, start, length)`	`SUBSTR(string, start, length)`
String Concatenation	`CONCAT(string1, string2)`	`CONCAT(string1, string2)` or `string1		string2`
Current Date/Time	`NOW()`	`NOW()`	`GETDATE()`	`SYSDATE` or `SYSTIMESTAMP`
Current Date	`CURDATE()`	`CURRENT_DATE`	`CURRENT_DATE`	`TRUNC(SYSDATE)`
Date Part Extraction	`YEAR(date)`, `MONTH(date)`	`EXTRACT(YEAR FROM date)`	`YEAR(date)`, `MONTH(date)`	`EXTRACT(YEAR FROM date)`
Date Addition	`DATE_ADD(date, INTERVAL value unit)`	`date + INTERVAL 'value unit'`	`DATEADD(unit, value, date)`	`date + INTERVAL 'value' unit`
Random Number	`RAND()`	`RANDOM()`	`RAND()`	`DBMS_RANDOM.VALUE`
Ceiling	`CEILING(number)`	`CEILING(number)`	`CEIL(number)`	`CEIL(number)`
Truncate	`TRUNCATE(number, decimals)`	`TRUNC(number::numeric, decimals)`	`ROUND(number, decimals, 1)`	`TRUNC(number, decimals)`

This cheatsheet provides a solid foundation for working with basic string, date, and math functions in SQL. Keep practicing and experimenting to master these essential skills!