In the world of SQL, numbers often get all the glory, but it’s the humble string functions that often hold the key to unlocking valuable insights from your data. Whether you’re cleaning up messy data imports, standardizing user inputs, or simply trying to extract meaningful patterns from your text fields, mastering SQL string functions is essential.
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!
Thanks for the list you shared yesterday. After a sit down with the branding team, We’ve noticed that our dish names are probably too long. But before we make any decisions we’d like to get the average length of the dish names per country. Can you provide that for me?
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. UPPER() and LOWER() Functions
The UPPER() and LOWER() functions convert a string to uppercase or lowercase, respectively. This is particularly useful for standardizing data, ensuring consistency in case-insensitive comparisons, and preparing data for case-insensitive searches.
| SYNTAX UPPER(string); LOWER(string); EXAMPLES: – Standardizing Names: Converting customer names to uppercase. SELECT UPPER(first_name), UPPER(last_name) FROM customers; – Search Optimization: Preparing search terms for case-insensitive searches. SELECT * FROM products WHERE UPPER(product_name) = UPPER(‘laptop’); – Data Cleaning: Ensuring email addresses are in uppercase. UPDATE customers SET email = LOWER(email); |
2. LENGTH() or LEN() Function
The LENGTH function returns the number of characters in a string. This can be useful for data validation, such as ensuring passwords meet length requirements, or for determining the length of various text fields in a database. The LENGTH() function (or LEN() in some databases) returns the length of a string.
| SYNTAX: LENGTH(string) EXAMPLES: – Password Validation: Checking if passwords meet the minimum length. SELECT username, LENGTH(password) AS password_length FROM users WHERE LENGTH(password) < 8; – Description Length: Finding products with descriptions that are too short or too long. SELECT product_name FROM products WHERE LENGTH(description) > 255; – Feedback Length: Ensuring customer feedback fits within a certain character limit. SELECT feedback_id FROM feedback WHERE LENGTH(comment) <= 280; |
SQL PRACTICE
Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like you to provide the average length of the dish names per country.
| SELECT r.country, AVG(LENGTH(o.dish_name)) as avg_length FROM restaurants r LEFT JOIN orders o ON r.id = o.restaurant_id GROUP BY r.country; |
FEEDBACK FROM THE CGO
Great! How about the global average length? That would be a good number to have as I walk into the meeting
Thanks!
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!
