Functions

The Scalar Function Toolkit

This guide provides a detailed, interview-focused breakdown of the most essential non-aggregate functions in MySQL, categorized for clarity.

1. String Functions: The Art of Text Manipulation

These functions are used to dissect, combine, and clean string data (VARCHAR, TEXT, etc.).

Function	Definition & Syntax	Real-World Example & Explanation
CONCAT & CONCAT_WS	CONCAT(str1, str2, ...) Joins strings together. CONCAT_WS(separator, str1, str2, ...) Joins strings with a separator.	Task: Create a FullName field for a customer report. SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Customers; Interview Insight: CONCAT has a major pitfall: if any argument is NULL, the entire result is NULL. The professional’s choice is often CONCAT_WS (Concatenate With Separator), which gracefully skips NULL values. CONCAT_WS(' ', FirstName, LastName) will still work even if one of the names is NULL.
UPPER & LOWER	UPPER(str) Converts to uppercase. LOWER(str) Converts to lowercase.	Task: Find a customer by email, ensuring the search is case-insensitive. SELECT CustomerID, Email FROM Customers WHERE LOWER(Email) = 'john.smith@example.com'; Interview Insight: This is a common technique to enforce consistency in WHERE clauses, especially when the table’s collation might be case-sensitive. It prevents you from missing a record because a user typed ‘John.Smith…’ instead of ‘john.smith…’.
LENGTH & CHAR_LENGTH	LENGTH(str) Returns the length in bytes. CHAR_LENGTH(str) Returns the length in characters.	Task: Find product names that are longer than 200 characters to flag them for review. SELECT ProductName FROM Products WHERE CHAR_LENGTH(ProductName) > 200; Interview Insight: The distinction is critical for multi-byte character sets like utf8mb4. A single emoji character might be 4 bytes long. LENGTH('😊') would return 4, but CHAR_LENGTH('😊') returns 1. For business rules based on what a user sees, always use CHAR_LENGTH.
SUBSTRING (SUBSTR)	SUBSTRING(str, start_pos, length) Extracts a substring. length is optional.	Task: Create a short product preview of 50 characters for a catalog page. SELECT ProductName, CASE WHEN CHAR_LENGTH(Description) > 50 THEN CONCAT(SUBSTRING(Description, 1, 50), '...') ELSE Description END AS ShortDescription FROM Products; Interview Insight: Very useful for creating summaries. Related functions LEFT(str, len) and RIGHT(str, len) are convenient shortcuts for SUBSTRING(str, 1, len) and SUBSTRING(str, CHAR_LENGTH(str) - len + 1, len) respectively.
TRIM	TRIM([removestr FROM] str) Removes leading and trailing spaces (or other characters). LTRIM and RTRIM are for left/right only.	Task: A user is trying to log in, but accidentally added a space to their email. Ensure the lookup works. -- Bad data: ' john.smith@example.com ' SELECT CustomerID FROM Customers WHERE Email = TRIM(' john.smith@example.com '); Interview Insight: This is a data sanitization essential. It demonstrates an understanding that user input is unreliable and must be cleaned before being used in comparisons to prevent bugs.
REPLACE	REPLACE(str, from_str, to_str) Replaces all occurrences of a substring. It is case-sensitive.	Task: Standardize product descriptions by replacing the outdated brand name “OldCorp” with “NewCorp”. UPDATE Products SET Description = REPLACE(Description, 'OldCorp', 'NewCorp') WHERE Description LIKE '%OldCorp%'; Interview Insight: Useful for bulk data cleaning operations or for formatting data for display (e.g., removing dashes from a phone number).
LOCATE	LOCATE(substr, str, [start_pos]) Finds the starting position of a substring. Returns 0 if not found.	Task: Find the username part of all customer emails. SELECT SUBSTRING(Email, 1, LOCATE('@', Email) - 1) AS Username FROM Customers; Interview Insight: LOCATE (or its synonym INSTR) is often used with SUBSTRING to perform more complex string dissections than LIKE can handle. It’s a more surgical tool.

2. Numeric Functions: The Tools of Calculation

Function	Definition & Syntax	Real-World Example & Explanation
ROUND	ROUND(number, [decimals]) Rounds a number to a specified number of decimal places.	Task: Display the product price with VAT (Value Added Tax) of 20%, rounded to 2 decimal places for the customer. SELECT Price, ROUND(Price * 1.20, 2) AS PriceWithVAT FROM Products; Interview Insight: Crucial for presentation logic. Remember that for actual financial storage, DECIMAL or NUMERIC data types are required to avoid floating-point errors. ROUND is for the final display.
CEIL & FLOOR	CEIL(number) Rounds up to the nearest integer. FLOOR(number) Rounds down to the nearest integer.	Task: We ship products in boxes that hold 6 items. Calculate how many boxes are needed for each order item. -- If quantity is 13, 13/6 = 2.16. We need 3 boxes. SELECT OrderItemID, Quantity, CEIL(Quantity / 6) AS BoxesNeeded FROM Order_Items; Interview Insight: These are fundamental for any logic involving discrete units like inventory, packaging, or resource allocation.
ABS	ABS(number) Returns the absolute (non-negative) value.	Task: Find orders where the recorded PriceAtPurchase differed from the product’s CurrentPrice by more than $5, regardless of direction. SELECT o.OrderID, p.Price, o.PriceAtPurchase FROM Order_Items o JOIN Products p ON o.ProductID = p.ProductID WHERE ABS(p.Price - o.PriceAtPurchase) > 5.00; Interview Insight: Useful for deviation and difference analysis where the direction of the change doesn’t matter.
MOD	MOD(N, M) or N % M Returns the remainder of N divided by M (modulo).	Task: Select every 100th customer for a special survey. SELECT CustomerID, FirstName FROM Customers WHERE MOD(CustomerID, 100) = 0; Interview Insight: This is a classic method for sampling data or creating alternating patterns (e.g., alternating row colors in a report MOD(row_number, 2)). It’s a clever, efficient problem-solver.

3. Date & Time Functions: The Language of Time

Function	Definition & Syntax	Real-World Example & Explanation
NOW & CURDATE	NOW() Returns the current DATETIME (‘YYYY-MM-DD HH:MM:SS’). CURDATE() Returns the current DATE (‘YYYY-MM-DD’).	Task: Find all orders placed today. SELECT OrderID, OrderDate FROM Orders WHERE DATE(OrderDate) = CURDATE(); Interview Insight: The DATE() function is used here to strip the time part from the OrderDate column, allowing for a clean comparison with CURDATE(). This is a crucial pattern for ‘today’s data’ queries.
DATE_FORMAT	DATE_FORMAT(date, format_specifier) Formats a date into a custom string.	Task: Create a human-readable order date for an invoice, e.g., “Monday, 25th August 2025”. SELECT OrderID, DATE_FORMAT(OrderDate, '%W, %D %M %Y') AS FormattedDate FROM Orders; Interview Insight: This is purely for presentation. A key principle of database design is to store date/time information in its native DATETIME or TIMESTAMP format and only use DATE_FORMAT in the final SELECT statement for display. Never store dates as strings.
DATEDIFF & TIMESTAMPDIFF	DATEDIFF(date1, date2) Returns the difference in days. TIMESTAMPDIFF(unit, start, end) Returns the difference in a specified unit (e.g., HOUR, MINUTE, MONTH).	Task: Find “inactive” customers who haven’t placed an order in over 180 days. SELECT CustomerID, MAX(OrderDate) AS LastOrderDate FROM Orders GROUP BY CustomerID HAVING DATEDIFF(CURDATE(), MAX(OrderDate)) > 180; Interview Insight: Note that I broke the “no aggregates” rule slightly here to show a truly realistic example. DATEDIFF is a workhorse for business logic like calculating subscription expiry or flagging stale records. TIMESTAMPDIFF is more flexible for finer-grained analysis.
DATE_ADD & DATE_SUB	DATE_ADD(date, INTERVAL value unit) Adds a time interval. DATE_SUB(date, INTERVAL value unit) Subtracts a time interval.	Task: Find all customer accounts that are less than 30 days old. SELECT CustomerID, RegistrationDate FROM Customers WHERE RegistrationDate > DATE_SUB(CURDATE(), INTERVAL 30 DAY); Interview Insight: This is the standard, professional way to perform date arithmetic. It is far more robust and readable than doing manual calculations. The INTERVAL syntax is powerful and clear.

4. Utility & Control Flow Functions

Function	Definition & Syntax	Real-World Example & Explanation
COALESCE	COALESCE(val1, val2, ...) Returns the first non-NULL value in the argument list.	Task: Display a customer’s shipping address. If the ShippingAddress is NULL, use their BillingAddress instead. If that’s also NULL, show ‘Address not provided’. SELECT COALESCE(ShippingAddress, BillingAddress, 'Address not provided') AS AddressToDisplay FROM Customers; Interview Insight: COALESCE is the ANSI-standard, more powerful version of IFNULL. It is an essential tool for providing default values and handling NULLs gracefully in reports. It’s a hallmark of a professional query.
NULLIF	NULLIF(expr1, expr2) Returns NULL if expr1 is equal to expr2, otherwise returns expr1.	Task: In a legacy Products table, “out of stock” was stored as the integer -1 in the StockQuantity column. Convert these to proper NULLs for a calculation. -- Avoids a divide-by-zero error or incorrect average SELECT AVG(NULLIF(StockQuantity, -1)) FROM Products; Interview Insight: NULLIF is a surgical tool for data cleaning. It’s perfect for situations where a “magic value” (like -1, 999, or an empty string) was used to represent missing data, allowing you to convert it back to the proper NULL representation.
CASE ... END	CASE [expr] WHEN ... THEN ... ELSE ... END A powerful if-then-else statement inside your SQL query.	Task: Categorize products into pricing tiers based on their price. SELECT ProductName, Price, CASE WHEN Price < 20.00 THEN 'Budget' WHEN Price BETWEEN 20.00 AND 100.00 THEN 'Standard' WHEN Price > 100.00 THEN 'Premium' ELSE 'Uncategorized' END AS PricingTier FROM Products; Interview Insight: CASE is arguably the most powerful control-flow tool in SQL. It allows you to embed complex business logic directly into your query to create derived columns, custom sort orders, or conditional aggregations. Mastering CASE is a significant step towards writing advanced, analytical SQL.