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.

Query 1. Combine customer first and last name columns to make a new 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 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 need 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;

😄 Fun Tip! You can also add in some extra text with CONCAT like this:

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

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  LENGTH 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 CONCAT. 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 with SELECT :
SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_name

And then add  LENGTH. 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;

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

SUBSTR  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!

#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 to 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. Here is a quick break-down of the steps we need:

  1. Use CONCAT to get the customers’ full name.
  2. Use CONCAT and SUBSTR together to retrieve the email.
  3. Use REPEAT and INSTR to censor it.
  4. 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

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, 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 :

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.