What are the Scalar Functions in SQL?

by

What are the Scalar Functions in SQL? Complete Guide

Scalar Functions in SQL are built-in functions that return a single value based on the input value. They operate on a single row and are frequently used to perform calculations, format data, and manipulate strings or dates in SQL queries. Scalar functions do not affect the data in the table but help present it in a more useful or readable format.

In this guide, we’ll explore different types of scalar functions in SQL with practical examples to help you use them effectively.

1. What Are Scalar Functions in SQL?

Scalar Functions return a single value for every row processed. They can be used in:

  • SELECT statements
  • WHERE and HAVING clauses
  • ORDER BY and GROUP BY clauses

Common Types of Scalar Functions:

  • String Functions: Operate on string values (e.g., UPPER(), LOWER(), CONCAT()).
  • Numeric Functions: Perform calculations (e.g., ROUND(), ABS(), CEILING()).
  • Date Functions: Manage date and time (e.g., GETDATE(), DATEPART(), DATEDIFF()).
  • Conversion Functions: Convert data types (e.g., CAST(), CONVERT()).

2. Overview of Common SQL Scalar Functions

Function CategoryCommon FunctionsPurpose
String FunctionsUPPER(), LOWER(), CONCAT(), SUBSTRING()Manipulate or format string data.
Numeric FunctionsROUND(), ABS(), CEILING(), FLOOR()Perform mathematical calculations.
Date FunctionsGETDATE(), DATEPART(), DATEDIFF()Handle date and time operations.
Conversion FunctionsCAST(), CONVERT()Convert data types between formats.
System FunctionsISNULL(), COALESCE(), @@IDENTITYProvide system-related information or handle NULLs.

3. String Functions in SQL

String functions help manipulate and format string data.

a) UPPER() and LOWER()

  • Converts strings to uppercase or lowercase.

Example:

sqlCopyEditSELECT UPPER('sql queries') AS UpperCase,
       LOWER('SQL QUERIES') AS LowerCase;

Output:

UpperCaseLowerCase
SQL QUERIESsql queries

b) CONCAT() Function

  • Joins two or more strings.

Example:

sqlCopyEditSELECT CONCAT('SQL', ' ', 'Functions') AS CombinedString;

Output:

CombinedString
SQL Functions

c) SUBSTRING() Function

  • Extracts a part of a string.

Example:

sqlCopyEditSELECT SUBSTRING('SQL Functions', 5, 9) AS Extracted;

Output:

Extracted
Functions

4. Numeric Functions in SQL

Numeric functions perform calculations and data formatting.

a) ROUND() Function

  • Rounds a number to a specified number of decimal places.

Example:

sqlCopyEditSELECT ROUND(123.456, 2) AS RoundedNumber;

Output:

RoundedNumber
123.46

b) ABS() Function

  • Returns the absolute value of a number.

Example:

sqlCopyEditSELECT ABS(-50) AS AbsoluteValue;

Output:

AbsoluteValue
50

c) CEILING() and FLOOR() Functions

  • CEILING() rounds up, FLOOR() rounds down.

Example:

sqlCopyEditSELECT CEILING(4.2) AS CeilingValue, FLOOR(4.8) AS FloorValue;

Output:

CeilingValueFloorValue
54

5. Date Functions in SQL

Date functions are used to handle date and time data.

a) GETDATE() Function

  • Returns the current system date and time.

Example:

sqlCopyEditSELECT GETDATE() AS CurrentDateTime;

b) DATEDIFF() Function

  • Returns the difference between two dates.

Example:

sqlCopyEditSELECT DATEDIFF(DAY, '2023-01-01', '2023-12-31') AS DaysDifference;

Output:

DaysDifference
364

c) DATEPART() Function

  • Extracts part of a date (year, month, day).

Example:

sqlCopyEditSELECT DATEPART(YEAR, '2023-12-31') AS YearPart;

Output:

YearPart
2023

6. Conversion Functions in SQL

Conversion functions change data types.

a) CAST() Function

  • Converts data types explicitly.

Example:

sqlCopyEditSELECT CAST(123.45 AS INT) AS ConvertedValue;

Output:

ConvertedValue
123

b) CONVERT() Function

  • Converts data types with formatting options.

Example:

sqlCopyEditSELECT CONVERT(VARCHAR, GETDATE(), 103) AS FormattedDate;

7. System Functions in SQL

System functions provide system information or handle NULLs.

a) ISNULL() Function

  • Replaces NULL with a specified value.

Example:

sqlCopyEditSELECT ISNULL(NULL, 'Default Value') AS Result;

Output:

Result
Default Value

b) COALESCE() Function

  • Returns the first non-null value.

Example:

sqlCopyEditSELECT COALESCE(NULL, NULL, 'First Non-Null') AS Result;

Output:

Result
First Non-Null

8. Best Practices for Using Scalar Functions

  • Avoid in WHERE Clause: Scalar functions can degrade performance if used in WHERE clauses.
  • Leverage Indexing: Indexes help when using scalar functions in SELECT statements.
  • Combine Functions Efficiently: Use a mix of string, numeric, and date functions for complex requirements.

9. Frequently Asked Questions (FAQs)

Q1: Can we create custom scalar functions in SQL?

A: Yes, you can use CREATE FUNCTION to define custom scalar functions.

Q2: Are scalar functions faster than aggregate functions?

A: It depends on the use case; scalar functions process row-by-row, which can sometimes be slower.

Final Thoughts

Mastering SQL Scalar Functions enhances your ability to manipulate and present data effectively. From formatting strings to handling dates and performing calculations, scalar functions are vital for writing optimized and clean SQL queries.

For more detailed SQL tutorials, visit our SQL Community.
Explore additional resources on SQL Scalar Functions.

1 thought on “What are the Scalar Functions in SQL?”

Comments are closed.