SQL Functions in SQL

The SQL Computation Engine Database engines do more than just store and filter raw bytes; they also contain a built-in calculation engine. SQL Functions allow you to manipulate text strings, perform mathematical calculations, parse calendar dates, and compute statistical aggregations directly on the database server. Running these operations at the database layer reduces the amount of raw data that needs to be transferred over the network, allowing your application layer to receive clean, pre-processed result sets. 1. Text Transformations: String Functions String functions reshape and clean up text data during a query. This is highly useful for standardizing formatting or stripping out unwanted characters before presenting data to the user interface. CONCAT(str1, str2, ...) : Joins multiple text strings together into a single cohesive string. SUBSTRING(string, start_position, length) : Extracts a specific section of text out of a larger string, starting at a designated character index. (Note: Unlike many programming languages where arrays start at 0, SQL string indexes start at 1 ). LENGTH(string) : Counts and returns the total number of characters contained within a text string. UPPER(string) / LOWER(string) : Converts all characters in a string to uppercase or lowercase, frequently used to normalize data for case-insensitive comparisons. TRIM(string) : Strips out all leading and trailing blank spaces from a text string. REPLACE(string, target, replacement) : Scans a text field and replaces every instance of a specific target substring with a new replacement string. SQL -- Standardizing and parsing messy string fields dynamically SELECT CONCAT(UPPER(node_role), '::', LOWER(node_name)) AS formatted_signature, SUBSTRING(assigned_ip, 1, 7) AS network_subnet_prefix, REPLACE(system_status, 'DEGRADED', 'ACTION_REQUIRED') AS operational_status FROM system_nodes WHERE LENGTH(TRIM(node_name)) > 0; 2. Mathematical Precision: Numeric Functions Numeric functions perform mathematical operations directly on integer and decimal columns, ensuring high numeric precision. ROUND(numeric_value, decimal_places) : Rounds a number to a specified number of decimal places based on standard rounding rules. CEIL(numeric_value) (or CEILING ): Rounds a number up to the nearest whole integer, regardless of the decimal value. FLOOR(numeric_value) : Rounds a number down to the nearest whole integer. ABS(numeric_value) : Returns the absolute (positive) value of a number, stripping away any negative signs. MOD(dividend, divisor) : Performs a modulo division operation, returning only the remainder that is left over. SQL -- Processing mathematical metrics with precision boundaries SELECT ROUND(latency_score, 2) AS rounded_latency, CEIL(bandwidth_usage_gb) AS billing_units_ceil, FLOOR(uptime_hours) AS completed_hours, MOD(node_id, 2) AS load_balancing_bucket FROM telemetry_metrics; 3. Temporal Logic: Date & Time Functions Because time zones, leap years, and variable month lengths make date math complicated, databases provide specialized temporal engines to handle date tracking and calendar arithmetic safely. NOW() : Returns the current server timestamp, including both the exact date and the active clock time. CURDATE() (or CURRENT_DATE ): Returns only the current date, leaving off the clock time parameters. DATE_ADD(date, INTERVAL value unit) : Adds a specific window of time (e. g., hours, days, or months) to an existing date value. (Note: In PostgreSQL, this is written using interval syntax: date + INTERVAL '3 days' ). DATEDIFF(end_date, start_date) : Calculates the exact number of days between two calendar dates. EXTRACT(field FROM timestamp) : Isolates and pulls out a single specific component (such as the year, month, day, or hour) from a full timestamp value. SQL -- Auditing time-sensitive operational logs SELECT node_id, EXTRACT(HOUR FROM created_at) AS activation_hour, DATEDIFF(NOW(), created_at) AS active_lifespan_days FROM system_nodes WHERE created_at >= DATE_ADD(CURDATE(), INTERVAL -30 DAY); -- Filter records from the last 30 days 4. Statistical Data Summaries: Aggregate Functions While standard functions operate on individual values row-by-row, Aggregate Functions scan an entire column across thousands of rows simultaneously, compressing that data down into a single summarized metric value. COUNT(column_name) : Counts the total number of non-NULL values inside a column. Using COUNT(*) counts every single row in the table, including empty or NULL fields. SUM(column_name) : Adds up the entire mathematical total of a numeric column. AVG(column_name) : Calculates the arithmetic mean average of a numeric column. MIN(column_name) / MAX(column_name) : Finds the absolute smallest or largest value inside a column. This works on numbers, text strings (alphabetical sorting), and date timestamps (earliest and latest dates). SQL -- Generating a high-level overview summary of server fleet performance SELECT COUNT(*) AS total_provisioned_nodes, COUNT(secondary_dns) AS configured_dns_count, -- Skips rows where secondary_dns is NULL SUM(traffic_weight) AS combined_fleet_capacity, AVG(traffic_weight) AS average_node_allocation, MIN(created_at) AS oldest_active_node_launch FROM system_nodes WHERE is_active = TRUE; SQL Functions Reference Matrix Function Category Classification Processing Operational Focus Row Input-to-Output Ratio Critical Production Guardrail String Functions Text cleaning, manipulation, parsing, and character adjustments. 1 → 1 (Processes one row input and returns one row output). Be careful when using text-altering functions like LOWER() or UPPER() inside a WHERE clause. Applying a function to a column prevents the engine from using standard indexes, dropping performance. Numeric Functions Mathematical transformations and precision rounding boundaries. 1 → 1 (Processes one row input and returns one row output). Always use ROUND explicitly when calculating or presenting fractional numbers to ensure consistency across different application UI components. Date Functions Calendar math, timezone tracking, and timestamp extraction. 1 → 1 (Processes one row input and returns one row output). Never treat dates as standard text strings. Always use native date arithmetic functions like DATE_ADD to ensure changes handle complex calendar boundaries safely. Aggregate Functions Compiles columnar statistics across multiple database records. Many → 1 (Compresses multiple input rows into a single summary output). All aggregate functions (except COUNT(*) ) automatically skip and ignore NULL values . If a column contains nulls, an average function ( AVG ) will calculate its mean using only the rows that contain actual numbers.