General Functions with SQL

General Functions with SQL

General functions are built-in operations provided by SQL databases to manipulate and analyze data. These functions cover various tasks, including string manipulation, numeric calculations, and date handling.

String Functions
UPPER: Converts a string to uppercase. 

SELECT UPPER('hello world') AS uppercase_string;

SELECT UPPER('hello world') AS uppercase_string;

​

 
Result: ‘HELLO WORLD’
LOWER: Converts a string to lowercase. 

SELECT LOWER('HELLO WORLD') AS lowercase_string;

xxxxxxxxxx

SELECT LOWER('HELLO WORLD') AS lowercase_string;

​

​

 Result: ‘hello world’
SUBSTR or SUBSTRING: Extracts a substring from a string. 

SELECT SUBSTR('hello world', 1, 5) AS substring;

xxxxxxxxxx

SELECT SUBSTR('hello world', 1, 5) AS substring;

​

 Result: ‘hello’
CONCAT: Concatenates two or more strings. 

SELECT CONCAT('hello', ' ', 'world') AS concatenated_string;

xxxxxxxxxx

SELECT CONCAT('hello', ' ', 'world') AS concatenated_string;

Result: ‘hello world’
TRIM: Removes leading and trailing spaces from a string.

SELECT TRIM(' hello world ') AS trimmed_string;

xxxxxxxxxx

SELECT TRIM(' hello world ') AS trimmed_string;

Result: ‘hello world’

Numeric Functions
ROUND: Rounds a number to a specified number of decimal places. 

SELECT ROUND(123.4567, 2) AS rounded_number;

SELECT ROUND(123.4567, 2) AS rounded_number;

​

 Result: 123.46
FLOOR: Rounds a number down to the nearest integer. 

SELECT FLOOR(123.4567) AS floored_number;

SELECT FLOOR(123.4567) AS floored_number;

Result: 123
CEIL or CEILING: Rounds a number up to the nearest integer. 

SELECT CEIL(123.4567) AS ceiled_number;

SELECT CEIL(123.4567) AS ceiled_number;

​

 Result: 124
ABS: Returns the absolute value of a number. 

SELECT ABS(-123.45) AS absolute_value;

SELECT ABS(-123.45) AS absolute_value;

​

 Result: 123.45

 Date Functions
SYSDATE or CURRENT_DATE: Returns the current date

SELECT SYSDATE AS current_date FROM dual;

SELECT SYSDATE AS current_date FROM dual;

​

Result: The current date.
ADD_MONTHS: Adds a specified number of months to a date. 

SELECT ADD_MONTHS(SYSDATE, 1) AS next_month FROM dual;

SELECT ADD_MONTHS(SYSDATE, 1) AS next_month FROM dual;

​

 Result: The date one month from today.
EXTRACT: Extracts a part of a date (e.g., year, month). 

SELECT EXTRACT(YEAR FROM SYSDATE) AS current_year FROM dual;

SELECT EXTRACT(YEAR FROM SYSDATE) AS current_year FROM dual;

Result: The current year.
DATEDIFF: Returns the difference between two dates (in SQL Server). 

SELECT DATEDIFF(day, '2024-01-01', SYSDATE) AS days_diff;

SELECT DATEDIFF(day, '2024-01-01', SYSDATE) AS days_diff;

Result: The number of days between January 1, 2024, and today.

Conditional Expressions in SQL
Conditional expressions allow you to execute different logic based on conditions within your SQL queries. The most common conditional expressions are CASE and DECODE.

CASE Expression
Purpose: Performs conditional logic within a SELECT statement, allowing you to return different values based on specific conditions.
Syntax: 

CASE
WHEN condition THEN result
[WHEN condition THEN result ...]
[ELSE default_result]
END

CASE

WHEN condition THEN result

[WHEN condition THEN result ...]

[ELSE default_result]

END

​

Examples:
Simple CASE: 

SELECT employee_id,
CASE department_id
WHEN 10 THEN 'HR'
WHEN 20 THEN 'IT'
WHEN 30 THEN 'Sales'
ELSE 'Other'
END AS department_name
FROM employees;

SELECT employee_id,

CASE department_id

WHEN 10 THEN 'HR'

WHEN 20 THEN 'IT'

WHEN 30 THEN 'Sales'

ELSE 'Other'

END AS department_name

FROM employees;

Description: Assigns a department name based on the department_id.
Searched CASE: 

SELECT employee_id,
salary,
CASE
WHEN salary < 3000 THEN 'Low'
WHEN salary BETWEEN 3000 AND 6000 THEN 'Medium'
ELSE 'High'
END AS salary_range
FROM employees;

SELECT employee_id,

salary,

CASE

WHEN salary < 3000 THEN 'Low'

WHEN salary BETWEEN 3000 AND 6000 THEN 'Medium'

ELSE 'High'

END AS salary_range

FROM employees;

Description: Categorizes salaries into ranges based on their values.

DECODE Function (Oracle SQL Specific)
Purpose: A simpler alternative to CASE for straightforward conditional logic.
Syntax: 

DECODE(expression, search_value, result [, default])

DECODE(expression, search_value, result [, default])

​

 Examples:
Simple DECODE:

SELECT employee_id,
DECODE(department_id, 10, 'HR', 20, 'IT', 30, 'Sales', 'Other') AS department_name
FROM employees;

SELECT employee_id,

DECODE(department_id, 10, 'HR', 20, 'IT', 30, 'Sales', 'Other') AS department_name

FROM employees;

​

Description: Returns different results based on the department_id.

Applying General Functions and Conditional Expressions in SELECT
You can combine general functions with conditional expressions to perform complex data manipulations and analyses in your SELECT statements.
Example 1: Formatting Dates and Conditional Logic 

SELECT employee_id,
TO_CHAR(hire_date, 'YYYY-MM') AS hire_month,
CASE
WHEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM hire_date) > 10 THEN 'Veteran'
ELSE 'New'
END AS experience_level
FROM employees;

SELECT employee_id,

TO_CHAR(hire_date, 'YYYY-MM') AS hire_month,

CASE

WHEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM hire_date) > 10 THEN 'Veteran'

ELSE 'New'

END AS experience_level

FROM employees;

Description: Formats the hire_date to display the year and month, and categorizes employees based on their years of experience.
Example 2: Conditional Formatting with String and Numeric Functions 

SELECT employee_id,
UPPER(CONCAT(first_name, ' ', last_name)) AS full_name,
ROUND(salary * 1.1, 2) AS adjusted_salary,
CASE
WHEN salary < 5000 THEN 'Underpaid'
WHEN salary BETWEEN 5000 AND 10000 THEN 'Average'
ELSE 'Well Paid'
END AS salary_status
FROM employees;

SELECT employee_id,

UPPER(CONCAT(first_name, ' ', last_name)) AS full_name,

ROUND(salary * 1.1, 2) AS adjusted_salary,

CASE

WHEN salary < 5000 THEN 'Underpaid'

WHEN salary BETWEEN 5000 AND 10000 THEN 'Average'

ELSE 'Well Paid'

END AS salary_status

FROM employees;

Description: Formats the full name, adjusts the salary by 10%, and categorizes the salary status based on its value.

Summary
General functions and conditional expressions are essential tools for manipulating and analyzing data in SQL. They allow you to format data, perform calculations, and apply logic to generate meaningful results. By combining these functions and expressions in SELECT statements, you can create complex queries that meet various analytical needs.