Scalar functions are those that take a single input value and return a single output value.
Sometimes, a simple SELECT Column is not enough. We often need to transform that column before sending it to the application.
Unlike aggregate functions (like SUM or COUNT) that collapse many rows into one, scalar functions are applied row by row. If your query returns 1,000 rows, the function will run 1,000 times.
Let’s look at the essential ones, divided by category.
String functions
String manipulation is probably the most common task. Cleaning names, formatting codes, or extracting parts of text.
Returns the number of characters in a string. Note: Ignores trailing whitespace.
SELECT LEN('Hello World'); -- Returns 10
Indispensable for normalizing data before comparing or displaying it.
SELECT
Name,
UPPER(Name) AS NameUpper,
LOWER(LastName) AS LastNameLower
FROM Customers;
Gets a portion of text. Its parameters are: Text, StartPosition, Length.
-- Extract the first 3 letters (e.g., "Lui")
SELECT SUBSTRING('Luis Llamas', 1, 3);
Replaces all occurrences of one text with another. Very useful for cleaning dirty data.
-- Change dashes to slashes
SELECT REPLACE('2023-10-01', '-', '/'); -- Returns '2023/10/01'
Users often accidentally add spaces at the beginning or end (' Luis ').
LTRIM: Removes spaces on the left.RTRIM: Removes spaces on the right.TRIM: (Since SQL Server 2017) Removes from both sides.
SELECT TRIM(' Dirty Data '); -- Returns 'Dirty Data'
Mathematics functions
Although the database is not a scientific calculator, we need to perform basic arithmetic operations and rounding.
Returns the positive value of a number.
SELECT ABS(-150.50); -- Returns 150.50
Rounds a number to a specific number of decimal places. Careful: Rounds mathematically (0-4 down, 5-9 up).
SELECT ROUND(123.4567, 2); -- Returns 123.4600
Unlike ROUND, these force rounding in one direction.
CEILING: Rounds up to the nearest integer.FLOOR: Rounds down to the nearest integer.
SELECT CEILING(10.1); -- Returns 11
SELECT FLOOR(10.9); -- Returns 10
For more complex calculations. POWER(Base, Exponent) and SQRT(Number).
SELECT POWER(2, 3); -- 2 to the power of 3 = 8
SELECT SQRT(16); -- Square root of 16 = 4
Date and Time functions
Date handling is often a nightmare in programming, but SQL Server has very powerful tools to make it easier.
Returns the current date and time of the server. It’s the function you’ll use to save the creation moment of a record (RegistrationDate).
SELECT GETDATE(); -- E.g.: 2023-10-05 14:30:00.123
Allows adding (or subtracting) time intervals to a date. Syntax: DATEADD(Interval, Quantity, Date).
-- Add 7 days to today (Due date in one week)
SELECT DATEADD(DAY, 7, GETDATE());
-- Subtract 1 month (One month ago)
SELECT DATEADD(MONTH, -1, GETDATE());
Calculates how much time has passed between two dates. Syntax: DATEDIFF(Interval, StartDate, EndDate).
-- Calculate age (Difference in years)
SELECT DATEDIFF(YEAR, '1990-05-20', GETDATE());
-- Days elapsed since the start of the year
SELECT DATEDIFF(DAY, '2023-01-01', GETDATE());
Be careful with DATEDIFF: This function counts boundary crossings. DATEDIFF(YEAR, '2023-12-31', '2024-01-01') returns 1, even though only one day has passed, because it crossed the year boundary.
They are shortcuts to get parts of a date.
SELECT
YEAR(OrderDate) AS Year,
MONTH(OrderDate) AS Month
FROM Orders;
Introduced in modern versions, returns the last day of the month for a given date. Extremely useful for billing.
-- Last day of the current month
SELECT EOMONTH(GETDATE());
Combined Example
Let’s put it all together in one query. Imagine we generate a user report.
SELECT
-- Text: Name in uppercase and we remove spaces
UPPER(TRIM(Name)) AS CleanName,
-- Text: Create a code with the first 3 letters
SUBSTRING(LastName, 1, 3) + CAST(UserID AS VARCHAR) AS UserCode,
-- Date: Calculate seniority in days
DATEDIFF(DAY, RegistrationDate, GETDATE()) AS DaysSeniority,
-- Mathematics: Round their balance
ROUND(AccountBalance, 0) AS RoundedBalance
FROM Users
WHERE Active = 1;
With these functions, we transform raw data into information ready for consumption. But there’s a small detail in the example above: CAST. What does it do there? That leads us to the next topic.
