SQL (Structured Query Language) functions are used to perform various operations on data stored in a relational database management system (RDBMS). Here are some SQL functions I use all the time:
COUNT
I use this function to count the number of rows in a table that meet a certain condition. SQL Server does show the count quicly at the bottom right of SSMS but not Oracle SQL Developer unfortunately. (If someone knows how to find that out more quickly, please let me know.
Example:
SELECT COUNT(*) FROM mytable WHERE condition;
-- or simply
SELECT COUNT(*) FROM mytable
SUM, AVG, MIN, MAX
These function is used to calculate the sum, average, min and mex of values in a column.
Example:
SELECT SUM(salary) FROM mytable;
SELECT AVG(salary) FROM mytable;
SELECT MAX(salary) FROM mytable;
SELECT MIN(salary) FROM mytable;
CONCAT
This function is used to concatenate two or more strings together. There are important differences between concatenating values in SQL server and Oracle.
Here is an example in SQL Server to concatenate several (2 or more) values.
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM mytable;
ALternatively, you can use the + sign in SQL Server
SELECT first_name + ' ' + last_name) AS full_name FROM mytable;
The + sign doesn’t work in Oracle, instead use ||. Here is an example
SELECT first_name || ' ' || last_name) AS full_name FROM mytable;
Unfortunately CONCAT in Oracle doesn’t take more than 2 arguments, so if you want to join 4 strings together, you have to use CONCAT within a CONCAT, within anather CONCAT, like this…. which is not very elegant. Just use the || operator instead.
SELECT CONCAT(CONCAT(CONCAT(first_name, ' '), last_name), ' ')
AS full_name FROM mytable;
TRIM()
- This function is used to remove whitespace from the beginning or end of a string.
Example:
SELECT TRIM(name) FROM mytable;
Otherwise I use Common Tables Expressions (CTEs), ROW_NUMBER() with the PARTITION BY clause, and CASE Statements quite a lot. You can read the article I’ve written about those.
Leave a Reply