The Ultimate Guide to Advanced SQL Functions

advance sql functions

Advanced SQL functions provide powerful tools for data manipulation, allowing more sophisticated and efficient queries.

String Functions:

CONCAT: Combines two or more strings.
Use case: Combining first and last names into a single field.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

LENGTH: Returns the length of a string.
Use case: Finding the length of user names.

SELECT LENGTH(username) FROM users;

SUBSTRING: Extracts a substring from a string.
Use case: Extracting the first three characters of user names.

SELECT SUBSTRING(username, 1, 3) FROM users;

REPLACE: Replaces occurrences of a specified string.
Use case: Replacing part of a string with another string.

SELECT REPLACE(username, 'old', 'new') FROM users;

UPPER/LOWER: Converts a string to upper or lower case.
Use case: Converting user names to uppercase.

SELECT UPPER(username) FROM users;

Date and Time Functions:

DATE: Extracts the date part of a datetime expression.
Use case: Getting just the date from a datetime value.

SELECT DATE(order_date) FROM orders;

TIME: Extracts the time part of a datetime expression.
Use case: Getting just the time from a datetime value.

SELECT TIME(order_date) FROM orders;

TIMESTAMP: Returns a datetime value.
Use case: Working with complete date and time values.

SELECT TIMESTAMP('2023-01-01 12:00:00');

DATEPART: Extracts a part of a date.
Use case: Extracting the year from a date.

SELECT DATEPART(year, order_date) FROM orders;

DATEADD: Adds a time/date interval to a date.
Use case: Adding 30 days to the order date.

SELECT DATEADD(day, 30, order_date) FROM orders;

Numeric Functions:

ROUND: Rounds a number to a specified number of decimal places.
Use case: Rounding prices to two decimal places.

SELECT ROUND(price, 2) FROM products;

CEILING: Returns the smallest integer greater than or equal to a number.
Use case: Rounding prices up to the nearest integer.

SELECT CEILING(price) FROM products;

FLOOR: Returns the largest integer less than or equal to a number.
Use case: Rounding prices down to the nearest integer.

SELECT FLOOR(price) FROM products;

ABS: Returns the absolute value of a number.
Use case: Getting the absolute value of profits.

SELECT ABS(profit) FROM sales;

MOD: Returns the remainder of a division.
Use case: Finding the remainder when dividing quantities.

SELECT MOD(quantity, 2) FROM inventory;
a person learning sql
A person learning sql

Conditional Functions:

CASE: Allows for conditional logic in queries.
Use case: Categorizing stock levels.

SELECT CASE WHEN quantity > 10 THEN 'High' ELSE 'Low' END AS stock_level FROM inventory;

COALESCE: Returns the first non-null value in a list.
Use case: Providing a default value for discounts.

SELECT COALESCE(discount, 0) FROM orders;

NULLIF: Returns NULL if the two arguments are equal.
Use case: Avoiding division by zero errors.

SELECT NULLIF(quantity, 0) FROM inventory;

By mastering advanced SQL functions, you’ll be able to handle complex queries and derive more meaningful insights from your data.

For more on SQL, read on data constraintsaggregate functions, SQL subqueries and more.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top