Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Mike Booth
Mike Booth
2,035 Points
Posted by Mike Booth
Mike Booth
2,035 Points

Reporting with SQL replacing strings challenge 1

I have a few questions on this one, first is with the challenge: https://teamtreehouse.com/library/reporting-with-sql/working-with-text/replacing-strings

here is my code and the error it is producing: SELECT email FROM customers WHERE REPLACE(email, "@", "<at>") AS obfuscated_email:

SQL Error: near "AS": syntax error

My second question is on the video, when he shows the code for changing the state he has an ="CA" after the REPLACE function but does not explain why

2 Answers

Steven Parker
Steven Parker
231,128 Points
Steven Parker
Steven Parker
231,128 Points

The REPLACE function and the alias (AS) should both be part of the SELECT clause. A WHERE clause is used to filter which rows are returned but not to define what will be displayed.

In the video, the REPLACE function is being used to filter the rows instead, in this case by state:

... WHERE REPLACE(state, "California", "CA") = "CA" -- he wrote this, but...
... WHERE state = "California" OR state = "CA"      -- this would do the same thing

In actual practice, I would probably do that the second way as it's more compact and I think a bit easier to read. But then the video is using it as an example of what the REPLACE function does.

Mike Booth
Mike Booth
2,035 Points
Mike Booth
Mike Booth
2,035 Points

Ok, I got the code to work, thank you, as for the other part of my question here is the link to the video, and the time stamp is 1:40, he does use the replace after a WHERE clause also https://teamtreehouse.com/library/reporting-with-sql/working-with-text/replacing-portions-of-text

Steven Parker
Steven Parker
231,128 Points
Steven Parker
Steven Parker
231,128 Points

I updated my answer above..