Databases Reporting with SQL Working with Text Practice Session

Jesse Dispoto
Jesse Dispoto
Front End Web Development Techdegree Graduate 14,537 Points

Replace question on SQL practice

On the second to last question on the SQL practice, for the replace question it say --- In all of the reviews, replace the text "public relations" with "PR" my answer being

select replace(review, "public relations", "PR") as review from reviews;

However, I am wondering why this query statement is in correct and won't return any results. Isn't it essentially doing the same thing? In the example in the previous video, Andrew does an example like this and it works fine. Am I doing something wrong?

select * from reviews where replace(review, "public relations", "PR") = "PR";

3 Answers

Pro Student 52,004 Points

Although it ran for me either way I would expect the alias to be in quotes. I had 5 records returned with your query with the first one showing the change: Basically Aliens but with a better PR manager.

Your first line of code is correct: "select replace(review, "public relations", "PR") as review from reviews;" outputs what you want - five entries with PR instead of public relations.

In your second line of code, you have created something very similar to the code Andrew created with "CA" and "California".

The difference I can see here is that in Andrew's example, the entire data in that range that he was changing was going to change to "CA", so it showed all the results for that state, without actually changing the data in question in the output (it still showed entries with both "CA" and "California" - i.e. not changing them).

So I think what I'm trying to say is that the difference is this condition you've imposed on it doesn't cover the whole of the review in question (the one about PR) like Andrew's example does, so it doesn't work as a logical statement and SQL is failing to find anything that fits that condition to output.

If you tried: SELECT * FROM reviews WHERE REPLACE(review, "public relations", "PR") = "Basically Aliens but with a better PR manager."; i.e. "if you replace public relations" with "PR", do you still get the original review, but with 'PR' instead of 'public relations' in it?" it outputs the only entry with that phrase in it, because the condition you've imposed is definitely true for the whole of that review entry, not just part of it like before. This is the only way that the REPLACE function is definitely going to turn out a true result for this condition, because the word has been replaced inside the entry but still exists in its original, lengthy format - not just as the word "PR" like in the code you've suggested. (Obviously this is wordy, not efficient, and requires an exact copy of the review from the table.)

It does not alter the data like it would if you'd used REPLACE() in the SELECT section like in the first one. Essentially, this type of approach doesn't really even do what the question wants from you either.

P.S. for Kris, the alias doesn't need to be in quotes to work if it is just one word.

Brooks G
Brooks G
5,273 Points

To extend the question a bit, why doesn't the following work?

SELECT * FROM reviews WHERE REPLACE(review, "public relations", "PR") LIKE "%PR%";

It returns a set of data, but it doesn't change "public relations" to "PR", and it only returned two rows (both from username: love). I thought that maybe it's just searching the reviews for "pr" and returning any review with that sequence of characters, but only one of the rows had "pr" anywhere in it ("pretty").

Everything else makes sense as far as the topic of this post & the solution goes, but couldn't make sense of that.