Advanced Functions and Expressions

Ready to supercharge your SQL queries? Today, we're diving into advanced functions and expressions that can make your data work for you. Let's jump right in!

Table of Contents

1. String Functions
2. Date and Time Functions
3. Mathematical Functions
4. CASE Statements

String Functions

Ever needed to tweak text data in your database? String functions are your go-to tools. Let's explore some common ones.

CONCAT

CONCAT combines two or more strings into one.

Example

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;

This query merges first and last names into a full name.

SUBSTRING

SUBSTRING extracts a part of a string.

Example

SELECT SUBSTRING(Email, 1, 5) AS EmailStart
FROM Users;

This gets the first five characters of each email address.

REPLACE

REPLACE swaps out part of a string with something else.

Example

SELECT REPLACE(PhoneNumber, '-', '') AS CleanNumber
FROM Contacts;

Here, we're removing dashes from phone numbers.

Date and Time Functions

Working with dates and times can be tricky. But with these functions, it's a breeze.

NOW

NOW returns the current date and time.

Example

SELECT NOW() AS CurrentDateTime;

This gives you the exact moment when the query runs.

DATEADD

DATEADD adds a specific interval to a date.

Example

SELECT DATEADD(day, 7, OrderDate) AS DeliveryDate
FROM Orders;

Calculating expected delivery dates by adding seven days to the order date.

DATEDIFF

DATEDIFF calculates the difference between two dates.

Example

SELECT DATEDIFF(day, StartDate, EndDate) AS Duration
FROM Projects;

Finding out how many days each project took.

Mathematical Functions

Need to crunch some numbers? SQL's got you covered.

ROUND

ROUND rounds a number to a specified number of decimal places.

Example

SELECT ROUND(Price, 2) AS RoundedPrice
FROM Products;

Rounding prices to two decimal places for display.

FLOOR and CEILING

FLOOR rounds a number down, while CEILING rounds it up.

Example

SELECT FLOOR(Discount) AS DiscountFloor, CEILING(Discount) AS DiscountCeil
FROM Sales;

Getting the nearest whole numbers for discounts.

CASE Statements

Want to add conditional logic to your queries? CASE statements let you do just that.

Basic Syntax

SELECT
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result3
    END AS AliasName
FROM TableName;

Example

SELECT OrderID,
    CASE
        WHEN Quantity >= 100 THEN 'Bulk Order'
        WHEN Quantity >= 50 THEN 'Large Order'
        ELSE 'Standard Order'
    END AS OrderType
FROM Orders;

Classifying orders based on quantity.