Mastering SQL STRING Functions – Part 3

Imagine trying to analyze customer feedback, product descriptions, or user comments without the ability to manipulate text data—it’s like trying to read a book in the dark! SQL string functions are your flashlight, illuminating the path to clear, concise, and meaningful data.

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 verdict is in, we’d like to shorten the dish names and the first step is to remove words like “Rice”. Ie. “Jollof Rice” will now be shortened to “Jollof”
Kindly do that for all the dishes and provide a fresh list of all our dishes after shortening the names.
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. TRIM() Function

The TRIM function removes leading and trailing spaces from a string.
We also have two types of the trim function: LTRIM() & RTRIM().
The LTRIM function removes leading spaces from a string which is useful when dealing with data that may have unintended spaces at the beginning. While the RTRIM function removes trailing spaces from a string.

SYNTAX
TRIM(string)

EXAMPLES:
– Data Import: Removing spaces from imported product codes.
SELECT TRIM(product_code)
FROM imported_products;

– Product Codes: Cleaning up product codes with leading spaces.
SELECT LTRIM(product_code) as clean_product_code
FROM products;

– Address Fields: Ensuring no trailing spaces in address fields.
SELECT RTRIM(address) as clean_address
FROM addresses;

2. REPLACE() Function

The REPLACE function substitutes all occurrences of a specified substring within a string with another substring. This is useful for data cleaning, such as correcting misspellings or updating outdated terminology in a database.

SYNTAX:
REPLACE(string, old_substring, new_substring)

EXAMPLES:
– Correcting Typos: Fixing common misspellings in product descriptions.
SELECT REPLACE(description, ‘colour’, ‘color’) as fixed_description
FROM products;

– Updating Terms: Changing old product names to new ones.
SELECT REPLACE(product_name, ‘OldProduct’, ‘NewProduct’) as updated_name
FROM products;

– Standardizing Formats: Replacing hyphens with spaces in phone numbers.
SELECT REPLACE(phone_number, ‘-‘, ‘ ‘) as clean_phone
FROM contacts;

3. INSTR() Function

The INSTR function returns the position of the first occurrence of a substring within a string. This is useful for tasks such as identifying the position of specific characters or substrings, which can then be used for further string manipulation.

SYNTAX:
INSTR(string, substring)

EXAMPLES:
– Finding Domains: Locating the “@” symbol in email addresses.
SELECT INSTR(email, ‘@’) as at_position
FROM customers;

– Product Codes: Finding the position of a hyphen in product codes.
SELECT INSTR(product_code, ‘-‘) as hyphen_position
FROM products;

– Feedback Analysis: Locating keywords in customer feedback.
SELECT INSTR(feedback, ‘excellent’) as keyword_position
FROM customer_feedback;

SQL PRACTICE

Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like you to remove the word “Rice” from the dish names and provide a fresh list of all dishes after shortening the names.

SELECT DISTINCT REPLACE(LOWER(dish_name), ‘rice’, ‘ ‘) as short_dish_name
FROM orders ;

FEEDBACK FROM THE CGO

Okay this looks interesting…
Can you recreate this for “Swallow” as well? For example, “Fufu Swallow” will now just be “Fufu”
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!