Using Single-Row Functions to Customize Output with SQL

Using Single-Row Functions to Customize Output

Single-row functions in SQL are powerful tools for manipulating and customizing the data returned by your queries. They operate on individual values and return a single result for each row. These functions can be categorized into several types, including string functions, numeric functions, date functions, and conversion functions.

String Functions

These functions allow you to manipulate and format strings.

UPPER and LOWER

• UPPER(string): Converts all characters in a string to uppercase.
• LOWER(string): Converts all characters in a string to lowercase.

SELECT UPPER(first_name) AS upper_name,
       LOWER(last_name) AS lower_name
FROM employees;

CONCAT and ||

• CONCAT(string1, string2, …): Concatenates multiple strings into one.
• string1 || string2: Concatenation operator (used in some databases like PostgreSQL).

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

SUBSTR (or SUBSTRING)

• SUBSTR(string, start_position, length): Extracts a substring from a string starting at a specified position.

SELECT SUBSTR(employee_id, 1, 3) AS emp_prefix
FROM employees;

LENGTH and TRIM

• LENGTH(string): Returns the length of a string.
• TRIM(string): Removes spaces from the beginning and end of a string. 

SELECT LENGTH(first_name) AS name_length,
       TRIM(last_name) AS trimmed_last_name
FROM employees;

Numeric Functions

These functions allow you to work with numerical values.

ROUND, FLOOR, and CEIL

• ROUND(number, decimal_places): Rounds a number to a specified number of decimal places.
• FLOOR(number): Rounds a number down to the nearest integer.
• CEIL(number): Rounds a number up to the nearest integer. 

SELECT ROUND(salary, 2) AS rounded_salary,
       FLOOR(salary) AS floored_salary,
       CEIL(salary) AS ceiled_salary
FROM employees;

 MOD

• MOD(number, divisor): Returns the remainder of dividing number by divisor. 

SELECT MOD(salary, 1000) AS salary_remainder
FROM employees;

Date and Time Functions

These functions help manipulate and format dates and times.

SYSDATE and CURRENT_DATE

• SYSDATE: Returns the current date and time from the system.
• CURRENT_DATE: Returns the current date without the time component. 

SELECT SYSDATE AS current_datetime,
       CURRENT_DATE AS current_date
FROM dual;

ADD_MONTHS, MONTHS_BETWEEN, and TRUNC

• ADD_MONTHS(date, number_of_months): Adds a specified number of months to a date.
• MONTHS_BETWEEN(date1, date2): Returns the number of months between two dates.
• TRUNC(date, ‘format’): Truncates a date to a specified format (e.g., year, month).

SELECT ADD_MONTHS(hire_date, 6) AS future_date,
       MONTHS_BETWEEN(SYSDATE, hire_date) AS months_since_hired,
       TRUNC(SYSDATE, 'YEAR') AS start_of_year
FROM employees;

Conversion Functions

These functions are used to convert between different data types.

• TO_CHAR, TO_NUMBER, and TO_DATE
• TO_CHAR(expression, ‘format’): Converts a value to a string with a specified format.
• TO_NUMBER(expression, ‘format’): Converts a string to a number.
• TO_DATE(string, ‘format’): Converts a string to a date with a specified format. 

SELECT TO_CHAR(hire_date, 'DD-MON-YYYY') AS hire_date_formatted,
       TO_NUMBER('12345.67', '99999.99') AS number_value,
       TO_DATE('2024-08-25', 'YYYY-MM-DD') AS date_value
FROM employees;

Conclusion

Single-row functions are essential for customizing and transforming data in SQL. They allow you to format query results according to specific needs and requirements, making your queries more powerful and flexible. Mastering these functions will help you write more effective and tailored SQL queries.