Using Character, Number, Date, and Analytical Functions in SQL SELECT Statements

Using Character, Number, Date, and Analytical Functions in SQL SELECT Statements

SQL provides a variety of functions to manipulate and analyze data. These functions are categorized into different types: character functions, numeric functions, date functions, and analytical functions. Each type of function serves specific purposes and can be used to perform complex operations on data.

Character Functions

Character functions operate on string data, allowing you to manipulate, format, and extract parts of text.

CHAR_LENGTH / LENGTH

• CHAR_LENGTH(string) or LENGTH(string): Returns the number of characters in a string.

SELECT first_name, LENGTH(first_name) AS name_length
FROM employees;

SUBSTRING / SUBSTR

• SUBSTRING(string FROM start FOR length) or SUBSTR(string, start, length): Extracts a substring from a string starting at a specific position.

SELECT SUBSTRING(email FROM 1 FOR 5) AS email_prefix
FROM employees;

UPPER / LOWER

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

SELECT UPPER(last_name) AS upper_last_name,
       LOWER(first_name) AS lower_first_name
FROM employees;

CONCAT / ||

• CONCAT(string1, string2, …): Concatenates multiple strings into a single string.
• string1 || string2: Concatenation operator used in some database systems.

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

TRIM

• TRIM([LEADING | TRAILING | BOTH] trim_character FROM string): Removes spaces or specified characters from the beginning, end, or both sides of a string. 

SELECT TRIM(' ' FROM middle_name) AS trimmed_middle_name
FROM employees

Numeric Functions

Numeric functions perform mathematical operations on numeric data, useful for calculations and aggregations.

ROUND

• ROUND(number, decimal_places): Rounds a number to a specified number of decimal places.

FLOOR and CEIL

• FLOOR(number): Returns the largest integer less than or equal to number.
• CEIL(number): Returns the smallest integer greater than or equal to number.

SELECT 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_modulus
FROM employees

POWER and SQRT

• POWER(number, exponent): Raises number to the power of exponent.
• SQRT(number): Returns the square root of number.

SELECT POWER(salary, 2) AS salary_squared,
       SQRT(salary) AS salary_sqrt
FROM employees;

Date and Time Functions

Date and time functions handle operations on date and time values, allowing for calculations and formatting.

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

• CURRENT_DATE: Returns the current date.
• CURRENT_TIME: Returns the current time.
• CURRENT_TIMESTAMP: Returns the current date and time, including time zone information.

SELECT CURRENT_DATE AS today,
       CURRENT_TIME AS current_time,
       CURRENT_TIMESTAMP AS now
FROM dual;

ADD_MONTHS and MONTHS_BETWEEN

• 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.

SELECT ADD_MONTHS(hire_date, 6) AS six_months_later,
       MONTHS_BETWEEN(SYSDATE, hire_date) AS months_since_hired
FROM employees;

TRUNC

• TRUNC(date, ‘format’): Truncates a date to a specified format, such as the start of the year or month.

SELECT TRUNC(SYSDATE, 'YEAR') AS start_of_year,
       TRUNC(SYSDATE, 'MONTH') AS start_of_month
FROM dual;

EXTRACT

• EXTRACT(part FROM date): Extracts a specified part from a date, such as year, month, or day.

SELECT EXTRACT(YEAR FROM hire_date) AS hire_year,
       EXTRACT(MONTH FROM hire_date) AS hire_month
FROM employees;

Analytical Functions

Analytical functions perform complex calculations over a set of rows while retaining the individual row details. These functions are useful for advanced data analysis.

PERCENTILE_CONT

• PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column): Returns the value at a specified percentile (e.g., 50th percentile for median) within an ordered set of data.

SELECT department_id,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department_id;

STDDEV

• STDDEV(column): Returns the standard deviation of values in a column.

SELECT department_id,
       STDDEV(salary) AS salary_stddev
FROM employees
GROUP BY department_id;

LAG

• LAG(column, offset, default) OVER (PARTITION BY partition_column ORDER BY order_column): Returns the value of column from a previous row defined by offset. Useful for comparing values between consecutive rows.

SELECT employee_id,
       salary,
       LAG(salary, 1) OVER (ORDER BY hire_date) AS previous_salary
FROM employees;

LEAD

• LEAD(column, offset, default) OVER (PARTITION BY partition_column ORDER BY order_column): Returns the value of column from a subsequent row defined by offset. Useful for forecasting and comparing future values.

SELECT employee_id,
       salary,
       LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

Conclusion

Using character, numeric, date, and analytical functions in SQL SELECT statements allows you to manipulate, analyze, and format data effectively. Each function type has specific applications and syntax, and mastering these functions can significantly enhance your ability to extract insights from your data.