4. SQL Functions for String and Date Manipulation

SQL is not only used to retrieve raw data from databases but also offers powerful functions for manipulating strings and dates. These built-in functions allow you to process and format data directly in your queries, minimizing the need for post-processing in other tools like Excel or Tableau. Below are some of the most important string and date functions you can use to enhance your SQL capabilities.

1. String Manipulation Functions

String manipulation is crucial when dealing with text data, such as customer names, product descriptions, or URLs. Here are some essential string manipulation functions in SQL:

a. LEFT()

The LEFT() function extracts a specified number of characters from the left side of a string.

SELECT LEFT('Bernardo', 3); -- Returns 'Ber'

b. RIGHT()

The RIGHT() function extracts a specified number of characters from the right side of a string.

SELECT RIGHT('Bernardo', 5); -- Returns 'nardo'

c. SUBSTR() or SUBSTRING()

SUBSTR() extracts a substring from a string starting at a specific position and for a specified length.

SELECT SUBSTR('Bernardo', 2, 4); -- Returns 'erna'

d. REPLACE()

The REPLACE() function replaces occurrences of a specified substring within a string with another substring.

SELECT REPLACE('SQL course', 'SQL', 'Data Science'); -- Returns 'Data Science course'

e. CONCAT()

CONCAT() combines two or more strings into one. Alternatively, you can use || to concatenate.

SELECT 'SQL' || ' course'; -- Returns 'SQL course'

f. SUBSTRING_INDEX()

This function extracts a substring from a string before a specific delimiter.

SELECT SUBSTRING_INDEX('www.getninjas.com.br', '.', 2); -- Returns 'www.getninjas'

g. LENGTH()

LENGTH() returns the length of a string in characters.

SELECT LENGTH('SQL course'); -- Returns 10

h. UPPER() and LOWER()

These functions convert a string to uppercase or lowercase, respectively.

SELECT UPPER('sql course'); -- Returns 'SQL COURSE'
SELECT LOWER('SQL COURSE'); -- Returns 'sql course'

2. Date Manipulation Functions

Date manipulation is essential for analyzing time-related data like orders, registrations, or events. SQL provides several functions for handling dates:

a. DATE()

The DATE() function converts a string into a date data type.

SELECT DATE('2024-09-05'); -- Returns a date object for '2024-09-05'

b. DATE_ADD()

DATE_ADD() adds a specified time interval (days, months, years) to a date.

SELECT DATE_ADD('2024-09-05', INTERVAL 1 DAY); -- Returns '2024-09-06'

c. DATEDIFF()

DATEDIFF() calculates the difference between two dates in days.

SELECT DATEDIFF('2024-09-10', '2024-09-05'); -- Returns 5

d. CURRENT_DATE() and CURRENT_TIMESTAMP()

• CURRENT_DATE() returns the current date without the time.
• CURRENT_TIMESTAMP() returns the current date and time.

SELECT CURRENT_DATE(); -- Returns today's date
SELECT CURRENT_TIMESTAMP(); -- Returns current date and time

3. Anatomy of a Complex SQL Query

Let’s revisit the full structure of a complex SQL query and understand how these functions work together. This query retrieves user emails and category names along with the number of orders placed by each user in a specific category:

SELECT users.email, categories.name, COUNT(*) AS num_orders
FROM users
INNER JOIN orders ON users.user_id = orders.user_id
INNER JOIN categories ON orders.category_id = categories.category_id
WHERE users.name = 'Anderson' AND users.cpf = '193...'
GROUP BY users.email, categories.name
HAVING COUNT(*) > 5
ORDER BY users.user_id DESC
LIMIT 10;

Breakdown of the Query:

• SELECT: Retrieves the user emails, category names, and counts the number of orders.
• FROM: Starts with the users table.
• INNER JOIN: Joins the orders and categories tables based on user IDs and category IDs.
• WHERE: Filters for users named Anderson and specific CPF values.
• GROUP BY: Groups the results by user email and category name.
• HAVING: Filters to include only users who have placed more than five orders in a category.
• ORDER BY: Orders the results by user ID in descending order.
• LIMIT: Limits the output to 10 rows.

Conclusion

String and date manipulation functions in SQL offer flexibility and power when dealing with complex datasets. These functions allow you to process, filter, and format your data directly within SQL queries, saving time and effort. Understanding and mastering these functions will help you perform more sophisticated data analysis and make more informed decisions.