Use CONCAT to get the customers’ full name.Here is a quick break-down of the steps we need: We’re going to use CONCAT and SUBSTR to censor the email. 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. When dealing with customer’s personal information, such as name, address, and phone number, we need to handle it without exposing it. Print a list of customer names and their 'censored' emails (using CONCAT, SUBSTR, REPEAT, LENGTH) So now we only see the first three characters of every name! MARY SMITH has become just MAR. 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 Rename this column as well: AS short_nameĬlose it with a FROM function: FROM customer Here is a visual demonstration of what SUBTR does: SUBSTR(CONCAT(first_name, “ “, last_name), 1, 3) Next, we need to indicate how many characters of the name we wanted to be printed. Since we want it to start at the very beginning, we will use the number 1. First, we need to state the start position. Here, we have two new factors to think about. Then plug in SUBSTR and inside of it, duplicate your CONCAT function. SELECT customer_id, CONCAT(first_name, “ “, last_name)ĭon’t forget to rename the new column: AS full_name Let’s keep building on our earlier query. It also allows you to set a starting position and specify how many characters you want to extract. SUBSTR is used to extract only a part of the full string. Let’s print out only three characters of the customer’s name (first three and last three). ![]() 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 Go ahead and rename the new column as Name Length and close the query: We want to count every character in the first and last names, not including space we added earlier, so we’re going to rewrite our CONCAT function for LENGTH. SELECT customer_id, CONCAT(first_name, “ “, last_name) AS full_nameĪnd then add LENGTH. So first set the columns you want to view with SELECT : Here is a visual break down of what this query does: But if CONCAT is used for numerical calculations, you won’t be able to reproduce this query. In the previous example, the CONCAT function's result was a string corresponding to the customer’s name ( full_name), so we can use it with no problems. You can use strings or string columns directly within the LENGTH function, but for this example, we will use CONCAT. This function accepts string data as an input value and calculates how many characters it has and output numbers (calculates based on bytes). Sometimes it is necessary to calculate the length of a string. Let’s count the length of the customer’s full name (characters in the first and last name). I am ", 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. SELECT customer_id, CONCAT(first_name, “ ”, last_name) AS full_name FROM customer Your full and final query will look like this: Make sure you separate all three with commas :Īnd finally, close it with the name of the table and a semicolon. To add a space between the two, we need to explicitly put one there and highlight it with double quotations (“ ”). We also want to see the full name, so let’s use CONCAT to connect the first and last names. Start with specifying which column you want to see: customer ID And to combine the two in MySQL, you need CONCAT. ![]() 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. The table shows the customer's first and last names stored in different columns. Combine customer first and last name columns to make a new name column (CONCAT) 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 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 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 We will cover string data based on MySQL. There are many functions for strings, and sometimes different databases such as Oracle, SQL Server, and MySQL have different methods. In this tutorial, we will learn about the functions you can use to manipulate string data easily.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |