20 common scalar functions in SQL

What are scalar functions in SQL ?

Scalar functions in SQL are functions that operate on individual values and return a single value. Unlike aggregate functions, which operate on sets of values and return a single summary value, scalar functions are applied to each row independently and return a transformed or computed value for that row. Scalar functions can be used in SELECT statements, WHERE clauses, ORDER BY clauses, and other parts of SQL queries to perform various operations on data.

Check out the list

1. ABS(): Returns the absolute value of a numeric expression.
2. CEILING(): Rounds a number up to the nearest integer.
3. FLOOR(): Rounds a number down to the nearest integer.
4. ROUND(): Rounds a number to a specified number of decimal places.
5. CAST(): Converts an expression from one data type to another.
6. CONVERT(): Converts an expression from one data type to another (SQL Server specific).
7. LEN() or LENGTH(): Returns the length of a string.
8. LOWER() or LCASE(): Converts a string to lowercase.
9. UPPER() or UCASE(): Converts a string to uppercase.
10. LTRIM(): Removes leading spaces from a string.
11. RTRIM(): Removes trailing spaces from a string.
12. TRIM(): Removes leading and trailing spaces from a string.
13. LEFT() or SUBSTRING(): Returns a specified number of characters from the beginning of a string.
14. RIGHT(): Returns a specified number of characters from the end of a string.
15. CHARINDEX() or INSTR(): Returns the starting position of a substring within a string.
16. CONCAT() or ||: Concatenates two or more strings.
17. REPLACE(): Replaces all occurrences of a specified substring within a string with another substring.
18. SUBSTRING() or MID(): Extracts a substring from a string.
19. DATEDIFF(): Returns the difference between two dates.
20. DATEPART(): Returns a specific part of a date (year, month, day, etc.).