[SQL Basic] How to work with String Functions in SQL —My SQL CONCAT, LENGTH, SUBSTR

SQLGate -

#17. Marketing/Data Analysis for Beginners

In this tutorial, we will learn about the functions you can use to manipulate string data easily and quickly. There are many kinds of functions for strings, and sometimes different databases such as Oracle, SQL Server, and MySQL have different methods. We will cover string data based on MySQL.

#Glossary:

📌Lesson 3: SELECT / FROM / WHERE
📌Lesson 4: ORDER BY
📌Lesson 5: AND / OR / IN
📌Lesson 6: LIKE
📌Lesson 7: AS /DISTINCT / IS NULL
📌Lesson 9: COUNT / SUM
📌Lesson 10: AVG / MAX / MIN
📌Lesson 11: GROUP BY / HAVING
📌Lesson 12: CASE
📌Lesson 13: INNER JOIN
📌Lesson 14: OUTER LEFT JOIN / OUTER RIGHT JOIN
📌Lesson 15: SUBQUERIES
📌Lesson 16: DATE_FORMAT / DATEDIFF

Query 1. Combine customer first and last name columns to make a new full name column (CONCAT)
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name FROM customer;
Query 2. Let’s count the length of the customer’s full name (LENGTH)
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, LENGTH(CONCAT(first_name, last_name)) AS length_name FROM customer;
Query 3. Let’s print out only three characters of the customer’s name (SUBSTR)
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, SUBSTR(CONCAT(first_name, “ “, last_name), 1, 3) AS short_name FROM customer;

Q1. Combine customer first and last name columns to make a new full name column (CONCAT)

First let’s look at the customer table.

SELECT * FROM customer;

The table shows the customer first and last names stored in different columns. You may need the full name together in more complicated queries, so it’s good to know how to connect the two into one line. And to combine the two in MySQL, you can use CONCAT.

#CONCAT

Start with specifying which column you want to see: customer ID
SELECT customer_id

We also want to see the full name, so let’s use CONCAT to connect the first name and the last name. To add a space between the two, we need to specifically put one there and highlight it with double quotations (“ ”). Make sure you separate all three with commas :
CONCAT(first_name, “ ”, last_name)

Rename this new column as Full Name:
AS full_name

And finally, close it with the name of the table and a semicolon. Your full and final query will look like this:
SELECT customer_id, CONCAT(first_name, “ ”, last_name) AS full_name FROM customer;

⭐️ Use F9 in SQLGate to format your query!!

You can also add in some extra text in the CONCAT query like this:

SELECT customer_id, CONCAT("Hello. I am ", first_name, “ “, last_name) AS full_name FROM customer;

Hi! Nice to meet you!

Q2. Let’s count the length of the customer’s full name (characters in the first and last name).

Sometimes it is necessary to calculate the length of a string. We’re going to use the LENGTH query to do so. This function accepts string data as an input value, calculates how many characters it has, and outputs numbers (calculates based on bytes).

#LENGTH

You can use strings or string columns directly within the LENGTH function, but for this example we will use the CONCAT query we just learned. In the previous example, the result of the CONCAT function was a string corresponding to the customer’s name (full_name) so we can use it with no problems. But if CONCAT is used for numerical calculations, you won’t be able to reproduce this query.

Here is a visual break down of what this query does:

So first set the columns you want to view in the SELECT function:
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name

And then add the LENGTH function. We want to count every character in the first and last name, not including the space we added earlier, so we’re going to rewrite our CONCAT function for LENGTH. Here’s what it should look like:
LENGTH(CONCAT(first_name, last_name))

Go ahead and rename the new column as Name Length and close the query:
AS name_length FROM customer;

Here’s what the final query looks like:

SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, LENGTH(CONCAT(first_name, last_name)) AS name_length FROM customer;

Great! But why did we do that? Why do we need to know the length of customer names? We have to understand this function for our Practice Time question. You’ll see it pop up again later. Let’s keep going for now~

Q3. Let’s print out only three characters of the customer’s name (first three and last three).

The SUBSTR function is used to extract only a part of the full string. It also allows you to set a starting position and specify how many characters you want to extract.

#SUBSTR

Let’s keep building on our earlier query. Start with SELECT:
SELECT customer_id, CONCAT(first_name, “ “, last_name)

Don’t forget to rename the new column:
AS full_name

Then plug in SUBSTR and inside of it, duplicate your CONCAT function. Here, we have two new factors to think about. First, we need to state the start position. Since we want it to start at the very beginning, we will use the number 1. Next we need to state how many characters of the name we wanted printed. Let’s say only 3 characters:
SUBSTR(CONCAT(first_name, “ “, last_name), 1, 3)

Here is a visual demonstration of what SUBTR does:

Rename this column as well:
AS short_name

Close it with a FROM function:
FROM customer;

Your final query should look like this:

SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, SUBSTR(CONCAT(first_name, “ “, last_name), 1, 3) AS short_name FROM customer;

So now we only see the first 3 characters of every name! MARY SMITH has become just MAR. 😃 Nice!

Let’s say we want to print only the last 3 characters of a string. How would we go about it? This is a bit tough. Since the length of all the names are different, we can’t count the exact position. But what we can do is go backwards! Just tell SQLGate you want to go back 3 positions on all the names and start there. We can do this by writing negative 3 (-3). It should look like this:

SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, SUBSTR(CONCAT(first_name, “ “, last_name), 1, 3) AS short_name FROM customer;

🔑 You can set your start position at the front of the string by using positive numbers, or set it at the back of the string by using negative numbers.

#Practice Time

Print a list of customer names and their censored emails (using CONCAT, SUBSTR, REPEAT, LENGTH)

When dealing with customer’s personal information, such as name, address, and phone number, we need handle it without exposing it. Using the functions we learned thus far, let’s use the customer table to replace the customer’s email with a string that can’t be identified.

We’re going to use CONCAT and SUBSTR to censor the email. First, use CONCAT to get the customers’ full name. Then use CONCAT and SUBSTR together to retrieve the email, use REPEAT and INSTR to censor it and use AS to rename the column.

📌REPEAT: repeat a string as many times as desired
📌INSTR: returns position of first occurrence of a string in another string

Here’s what the result looks like:

Feeling a bit confused? Let’s break it down:

Start with your SELECT function and follow it up with the first column you want to view, the Customer ID column:
SELECT customer_id

Use CONCAT to combine the first and last name of customers, and rename the new column as full name:
CONCAT(first_name, “ “, last_name) AS full_name

Use CONCAT again to create the secret email column using these steps:

  1. Print the first 3 letters of a customers email using SUBSTR
    (SUBSTR(email, 1, 3)
  2. With INSTR we will identify a string (email) and specify a certain character (@)as the starting position we want in the string.
    INSTR(email, ‘@’)
  3. Now since we want to censor the email, we need to put asterisks (*) in to fill the remaining length (characters) after the original 3 we printed. Because that length is different for every email, we need to repeat it as many times as necessary, so let’s use REPEAT. We’re also going to subtract 1 (-1) for the @ symbol, and subtract 3 (-3) for the 3 printed text of the email, so that information doesn’t get censored.
    REPEAT(‘*’, INSTR(email)-1-3)
  4. Add the domain name to the string and close the CONCAT function:
    ‘@sakilacustomer.org’)
  5. Rename the new column and write the name of the table:
    AS secret_email FROM customer;

When you run the query (F5) you will get the first 3 characters of their email printed, followed by asterisks that censor the rest of the personal information! Here is the full query out together:

SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name, CONCAT(SUBSTR(email, 1, 3), REPEAT(‘*’, INSTR(email, ‘@’)-1–3), ‘@sakilacustomer.org’) AS secret_email FROM customer;

It may seem somewhat complex, but it’s not difficult to define each string you want to see at once and then combine it with the CONCAT function. Though we only demonstrated a commonly used practice of these functions, there are many other ways to use them. Experiment with them to find the right

If you like more visual guidance, check out our video tutorial below:

# Tutorial Video

Download now!

# [SQL Basic Series] — Marketing/Data Analysis for Beginners

1. What is SQL? — Database Introduction
2. Downloading SQLGate and Connecting to a Database
3. What kind of data is in the ‘film’ table? — SELECT/FROM/WHERE
4. How to sort film lists by price (rental rate)? — ORDER BY
5. Can I rent a 3 hour movie for $0.99? — AND/OR/IN
6. How can I find a movie that’s thrilling? — LIKE
7. How to rename columns and see unique values? — AS/DISTINCT/IS NULL
8. Let’s see Sample Database tables!
9. What is the total length of all the films added together? — COUNT/SUM
10. What is the average film length? — AVG/MIN/MAX
11. How many movies have the same rating? — GROUP BY/HAVING
12. Let’s designate a value based on the rental duration. — CASE
13. Let’s find out the movie genres by joining tables! — INNER JOIN
14. Let’s check movie rentals by the inventory list! — LEFT/RIGHT JOIN
15. Let’s find customers that have rented movies priced $9.99! — Subquery
16. How to work with Date Functions in SQL — DATE_FORMAT, DATEDIFF
17. How to work with String Functions in SQL — CONCAT, LENGTH, SUBSTR
18. Let’s use some Numeric SQL functions — TRUNCATE, ROUND, MOD
19. Let’s learn some advanced CASE queries!
20. Let’s learn some advanced JOIN queries!