Scalar Functions In Sql in SQL
? Scalar Functions in SQL
Scalar functions operate on a single value and return a single value. They are used to perform calculations, manipulate strings, dates, or numbers, and return one result per input row.
Categories of Scalar Functions:
| Category | Description | Examples |
|---|---|---|
| String Functions | Manipulate text strings | UPPER(), LOWER(), LTRIM(), RTRIM(), SUBSTRING() |
| Numeric Functions | Perform calculations on numeric values | ROUND(), ABS(), CEILING(), FLOOR(), POWER() |
| Date/Time Functions | Work with date and time values | GETDATE(), DATEADD(), DATEDIFF(), YEAR(), MONTH() |
| Conversion Functions | Convert between data types | CAST(), CONVERT() |
| System Functions | Return system info | USER(), DATABASE(), SESSION_USER() |
| Miscellaneous Functions | Other functions | COALESCE(), NULLIF(), ISNULL() |
? Examples of Scalar Functions
String:
SELECT UPPER('hello') AS uppercase_string; -- Returns 'HELLO'SELECT SUBSTRING('abcdef', 2, 3) AS substr; -- Returns 'bcd'Numeric:
SELECT ROUND(123.4567, 2) AS rounded_val; -- Returns 123.46SELECT ABS(-10) AS absolute_val; -- Returns 10Date/Time:
SELECT YEAR(GETDATE()) AS current_year; -- Returns current yearSELECT DATEADD(day, 7, GETDATE()) AS next_week; -- Adds 7 days to current date? Notes:
Scalar functions are evaluated for each row in a query.
Using scalar functions can affect query performance if overused in large datasets.
Most SQL dialects support these common scalar functions with slight syntax variations.
Would you like me to list specific scalar functions for a particular SQL dialect (e.g., MySQL, SQL Server, Oracle)?
