Text data is everywhere, from customer reviews to email addresses, and even a slight misstep in handling these strings can lead to inaccurate analysis. That’s where SQL string functions come into play. These powerful tools allow you to manipulate and refine text data with precision, ensuring that your analyses are as accurate as possible.
There are many ways to build SQL competency so we have decided to take a different approach.
For the next few days, you will be serving as the newly hired Data Analyst for Basira and Co, An Afro fusion restaurant with a network of chains across Africa and you’ll be reporting to the Chief Growth Officer (CGO) Mrs Ahmadu.
Everyday, you’ll get analytical requests/tasks from her related to the SQL topic of the day. The goal for this is to make each lesson as practical and as relatable as possible.
HOW TO PRACTICE: Here is a link to our practice guide. We have already prepared the table schema in advance so all you need to do is plug that into DBFiddle.com and you are all set! The LinkedIn forum is a great way to share any comments or questions about the practice guide.
YOU’VE GOT MAIL!
Hello!
The CEO just gave some feedback for the menu revamp request. Can we get a list of the dishes we serve and their prices? The results should be provided as text so this time around not in a tabular form.
Best Regards,
Mrs Ahmadu (CGO)
SQL TOPIC OF THE DAY
SQL string functions are a set of built-in operations that allow for manipulation and analysis of character or string data within the database. They provide a wide range of capabilities for string handling. Below are a few of the commonly Used SQL string functions:
1. CONCAT() Function
The CONCAT function in SQL is used to join two or more strings together into a single string. This is particularly useful when you want to combine different columns or literals into a single string. The syntax is straightforward: you simply list the strings or columns you want to concatenate separated by commas. Below is it’s syntax and some examples:
| SYNTAX CONCAT(string1, string2, …, stringN) |
– Customer Names: Combining first and last names to create a full name.
| SELECT CONCAT(first_name, ‘ ‘, last_name) as full_name FROM employees; |
– Dynamic Messages: Generating personalized greetings.
| SELECT CONCAT(‘Hello, ‘, customer_name, ‘! Welcome back.’) as message FROM customers; |
– Product Descriptions: Combining product attributes.
| SELECT CONCAT(product_name, ‘ – ‘, product_size, ‘ – ‘, product_color) as full_description FROM products; |
2. SUBSTRING() or SUBSTR() Function
The SUBSTRING() function extracts a portion of a string. It takes three arguments: the string, the starting position, and the length of the substring. This is useful for retrieving parts of strings, such as area codes from phone numbers or domain names from email addresses. Below is it’s syntax and some examples:
| SYNTAX: SUBSTRING(string, start_position, length) |
– Email Domain Extraction: Extracting the domain part of an email address.
| SELECT SUBSTRING(email, INSTR(email, ‘@’) + 1) as domain FROM customers; |
– Phone Numbers: Extracting area codes from phone numbers.
| SELECT SUBSTRING(phone_number, 1, 3) as area_code FROM contacts; |
– Product Codes: Getting the category code from a product identifier.
| SELECT SUBSTRING(product_id, 1, 4) as category_code FROM products; |
SQL PRACTICE
Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like you to provide a list of the dishes we serve and their prices in text form.
| SELECT r.country, CONCAT(o.dish_name, ‘ – ‘, o.price_paid) as dish_and_price FROM restaurants r LEFT JOIN orders o ON r.id = o.restaurant_id; |
FEEDBACK FROM THE CGO
Apologies, we actually need that only for the dishes sold in Nigeria. Can I have that before you call it a day?
Warm regards,
Mrs Ahmadu (CGO
With what we learned today, think you can solve this part on your own?
Did you learn anything new? Share your new insights with us on Facebook, LinkedIn or Instagram and don’t forget to tag us!
