Using Different Types of Functions in SQL

Using Different Types of Functions in SQL

SQL offers a wide range of functions that can be classified into several types: string functions, numeric functions, date and time functions, and conversion functions. Each type of function is designed to perform specific tasks and can be used to manipulate and format data as needed.

String Functions

String functions are used to manipulate and operate on string data. They are essential for text processing tasks.

Length and Position

• LENGTH(string): Returns the number of characters in the string.
• CHAR_LENGTH(string): Same as LENGTH, returns the number of characters.
• INSTR(string, substring): Returns the position of the first occurrence of substring within string. Returns 0 if substring is not found.

SELECT LENGTH(first_name) AS name_length,
       CHAR_LENGTH(last_name) AS last_name_length,
       INSTR(email, '@') AS at_position
FROM employees;

Substring and Concatenation

• SUBSTR(string, start_position, length): Extracts a substring from string starting at start_position for length characters.
• CONCAT(string1, string2, …): Concatenates multiple strings into a single string.
• ||: Concatenation operator (alternative to CONCAT, used in some databases).

SELECT SUBSTR(first_name, 1, 3) AS short_name,
       CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Case Conversion and Trimming

• UPPER(string): Converts all characters in string to uppercase.
• LOWER(string): Converts all characters in string to lowercase.
• TRIM([LEADING | TRAILING | BOTH] trim_character FROM string): Removes spaces or specified characters from the beginning, end, or both sides of string.

SELECT UPPER(first_name) AS upper_name,
       LOWER(last_name) AS lower_name,
       TRIM(' ' FROM middle_name) AS trimmed_middle_name
FROM employees;

Replacement and Formatting

• REPLACE(string, search_string, replace_string): Replaces occurrences of search_string in string with replace_string.
• REGEXP_REPLACE(string, pattern, replace_string): Replaces substrings matching a regular expression pattern with replace_string.

SELECT REPLACE(phone_number, '-', '') AS cleaned_phone
FROM employees;

Numeric Functions

Numeric functions perform operations on numeric values and are often used in calculations and aggregations.

Basic Arithmetic

• ABS(number): Returns the absolute value of number.
• ROUND(number, decimal_places): Rounds number to decimal_places decimal places.
• 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 ABS(salary) AS absolute_salary,
       ROUND(salary, 2) AS rounded_salary,
       FLOOR(salary) AS floored_salary,
       CEIL(salary) AS ceiled_salary
FROM employees;

Mathematical Functions

• POWER(number, exponent): Raises number to the power of exponent.
• SQRT(number): Returns the square root of number.
• MOD(number, divisor): Returns the remainder when number is divided by divisor. 

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

Date and Time Functions

Date and time functions are used to manipulate and format date and time values.

Current Date and Time

• SYSDATE: Returns the current date and time from the system.
• CURRENT_DATE: Returns the current date.
• CURRENT_TIME: Returns the current time.
• CURRENT_TIMESTAMP: Returns the current date and time with time zone information.

SELECT SYSDATE AS current_datetime,
       CURRENT_DATE AS current_date,
       CURRENT_TIME AS current_time,
       CURRENT_TIMESTAMP AS current_timestamp
FROM dual;

Date Arithmetic

• ADD_MONTHS(date, number_of_months): Adds a specified number of months to date.
• MONTHS_BETWEEN(date1, date2): Returns the number of months between date1 and date2.
• NEXT_DAY(date, ‘day’): Returns the date of the next specified day of the week after date.
• TRUNC(date, ‘format’): Truncates 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,
       NEXT_DAY(hire_date, 'SUNDAY') AS next_sunday,
       TRUNC(SYSDATE, 'YEAR') AS start_of_year
FROM employees;

Extracting Parts of Dates

• EXTRACT(part FROM date): Extracts a specified part (e.g., year, month, day) from date.

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

Conversion Functions

Conversion functions are used to convert data from one type to another.

String to Number and Date

• TO_CHAR(expression, ‘format’): Converts expression to a string with a specified format.
• TO_NUMBER(expression, ‘format’): Converts expression to a number with a specified format.
• TO_DATE(string, ‘format’): Converts string to a date using 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

Implicit and Explicit Conversions

• Implicit Conversion: Automatic conversion performed by SQL when the data types are compatible.
• Explicit Conversion: Conversion performed using conversion functions like TO_CHAR, TO_NUMBER, and TO_DATE.

-- Implicit conversion example
SELECT salary + 1000 AS increased_salary
FROM employees;
-- Explicit conversion example
SELECT TO_CHAR(salary) AS salary_as_string
FROM employees;

Conclusion

Understanding and effectively using these SQL functions can greatly enhance your ability to manipulate and customize your data. Each function has its specific use case and can be combined in various ways to achieve the desired results. Always refer to the documentation of your SQL database for specifics, as the available functions and their syntax can vary between different database systems.