Databases Reporting with SQL Working with Text Concatenating Text

Steve Rogers
Steve Rogers
3,071 Points

Problem with code

I am not sure why this string was not working. Any idea?

Steve Rogers
Steve Rogers
3,071 Points

Whoops, I thought it would attach.

In the library database there's a patrons table listing all the users of the library. The columns are id, first_name, last_name, address, email, library_id and zip_code. Generate a list of strings that are in the following format: Andrew Chalkley andrew@teamtreehouse.com. Concatenate the first name, last name and email address for all users.

Alias it to to_field. This will be used in the "To" field in email marketing.

and my code was ...

SELECT first_name ||" "|| last_name ||" "|| <email> AS "to_field" FROM patrons;

Thanks!

3 Answers

KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,293 Points

The angle brackets aren't included in the email field name and need to be concatenated as well:

SELECT first_name ||" "|| last_name ||" <"|| email || ">" AS "to_field" FROM patrons;
Steve Rogers
Steve Rogers
3,071 Points

got it. thanks! One question is, why is there a space after the concatenate symbol, before you add in the close angle bracket: email || ">" . It worked, but I would have thought this would create a space after the email name and the bracket in the result.

Thanks again!

KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,293 Points

You add a space by concatenating a space as shown with the opening bracket. The space between the symbol and what is being concatenated doesn't matter. The following produce the same results:

SELECT  first_name || " " ||         last_name AS "Full Name" FROM customers;
SELECT  first_name || " " ||last_name AS "Full Name" FROM customers;

Interesting in testing I found with SQLite only a single space is concatenated regardless of space or spaces. Both of the following also have the same result as above:

SELECT  first_name || "        " || last_name AS "Full Name" FROM customers;
SELECT  first_name || " " || " " || last_name AS "Full Name" FROM customers;

Whereas MySQL will concatenate the number of spaces provided

SELECT CONCAT(first_name, "        ", last_name) AS "Full Name" FROM customers

results in:

'JOHN        DOE'
Steve Rogers
Steve Rogers
3,071 Points

That's extremely helpful! Thanks so much.