SQL STRING FUNCTIONS With Practice Exercises

String-text functions are incredibly useful for cleaning your data. These functions will help you manipulate and transform text-based data effortlessly.

Tidying Up Data with UPPER() and LOWER()

The function capitalizes every letter in a string, while the function does the opposite, converting them to lowercase.

Here's an example query:

Running this would yield the following result:

In the real world, we use these functions because text data is incredibly messy. For example, names are often stored with inconsistent capitalization. By applying or , you can standardize all names to uppercase or lowercase, making it easier to sort the text, or search for strings matching a pattern using .

SQL LOWER Practice Exercise

Write a SQL query which uses and to find a customer's details whose name ends with "son".

Extracting Substrings with LEFT() and RIGHT()

The function extracts you the initial characters from a string, while the function retrieves the last characters.

Syntax

Example

In the real world, we often extract the first few or last few characters of a product ID to determine its category or manufacturer. For example, when dealing with a flight number like "UA93", using helps us extract "UA", which tells us the carrier is United Airlines.

Calculating String Length with LENGTH()

The function simply returns the length of a string, including the empty space in between.

Example

In the real world, we might use in a product review system, to sort customer reviews based on length. Our intuition here would be that longer reviews are likely more helpful and detailed, whereas, short reviews with just 5-6 characters indicate a low-quality review.

Removing Spaces using TRIM(), LTRIM(), RTRIM(), and BTRIM()

The function removes spaces from both ends of a string. Additionally, we have three more functions to handle trimming from specific sides:

• removes spaces (or specified characters) from the beginning of a string.
• removes spaces (or specified characters) from the end of a string.
• removes specified characters from both the beginning and the end of a string.

Here's an sample of and it's variants:

In the real-world, users entering their names or emails might accidentally include extra spaces. Applying these functions ensures that user input is clean and properly formatted.

Concatenating Strings with CONCAT()

The function combines two or more strings into one.

Syntax

Example

Real-world Scenario: When registering users, you can concatenate their first name and last name to generate their email addresses, like "nick.singh@datalemur.com".

CONCAT SQL Interview Question

Let's practice with a real SQL Interview question asked to a Data Analyst at CVS Pharmacy. You're asked to write a query to calculate the total sales for each manufacturer. Round the answer to the nearest million and report your results in descending order of total sales.

Your output should look something like this:

Slicing and Dicing using SUBSTRING()

The function returns a portion of a string based on the specified starting position and length. With a negative index, counts characters from the end of the string, offering a different perspective compared to counting from the beginning.

Syntax

Example

In , using a negative start position of -1, the function starts counting from the end of the string and returns the last character, which is 'n'.

In , with a negative start position of -2 and a length of 3, the function starts counting from the second-to-last character ('d') and returns the subsequent three characters, resulting in 'dow'.

Using negative indices provides an easy way to count characters from the end of the string. Just remember that -1 represents the last character, -2 represents the second-to-last character, and so on.

Real-world Scenario: Analyzing server log files, you can use to extract timestamps or error codes, making it easier to identify and troubleshoot issues.

What's Next: Instacart Analytics Case Study

We've now covered pretty much every major SQL concept needed to Ace the SQL Interview. In the process, you've solved dozens of smaller SQL interview questions.

Now, it's time to everything we've learned to an open-ended Instacart SQL Analytics case study!