The Ultimate Guide to Advanced SQL Functions

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

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

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;

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.

Related Posts