Understanding Scalar Functions in SQL

Scalar functions in SQL return a single value based on the input values. These functions operate on individual values rather than sets of rows.

Common Scalar Functions

LEN(): Returns the length of a string.
UPPER(): Converts a string to uppercase.
LOWER(): Converts a string to lowercase.
ROUND(): Rounds a number to a specified decimal place.
GETDATE(): Returns the current date and time.

Example Usage of Scalar Functions

1. Using LEN() Function

SELECT LEN('Hello World') AS StringLength;

Output.11

2. Using UPPER() and LOWER() Functions

SELECT UPPER('hello') AS UpperCase, LOWER('WORLD') AS LowerCase;

Output

UpperCase	LowerCase
HELLO	world

3. Using ROUND() Function

SELECT ROUND(123.456, 2) AS RoundedValue;

Output.123.46

4. Using GETDATE() Function

SELECT GETDATE() AS CurrentDateTime;

Output. 2024-03-19 14:30:00.000 (Example output, actual output may vary)

5. Using ABS() Function

SELECT ABS(-25) AS AbsoluteValue;

Output. 25

6. Using SQRT() Function

SELECT SQRT(49) AS SquareRoot;

Output. 7

7. Using SUBSTRING() Function

SELECT SUBSTRING('SQL Functions', 5, 9) AS SubstringResult;

Output. Functions

8. Using REPLACE() Function

SELECT REPLACE('Hello SQL', 'SQL', 'World') AS ReplacedString;

Output. Hello World

Advanced Use of Scalar Functions

1. Combining Scalar Functions

SELECT UPPER(LEFT('advanced scalar functions', 8)) AS Result;

Output. ADVANCED

2. Using Scalar Functions in Computations

SELECT ROUND(AVG(Salary), 2) AS AverageSalary FROM Employees;

Output. Returns the average salary rounded to 2 decimal places.

3. Formatting Dates Using Scalar Functions

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;

Output.2024-03-19 (Example output)

4. Custom Scalar Function Example

CREATE FUNCTION dbo.Getfullname(@FirstName NVARCHAR(50),
                                @LastName  NVARCHAR(50))
returns NVARCHAR(100)
AS
  BEGIN
      RETURN ( @FirstName + ' ' + @LastName )
  END;

Usage

SELECT dbo.GetFullName('John', 'Doe') AS FullName;

Output. John Doe

Advantages of Scalar Functions

Helps in data formatting and transformation.
Improves code readability and maintainability.
Enhances query flexibility with built-in SQL functions.

Scalar functions are essential for manipulating individual values in SQL queries.