Replace question on SQL practice

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.

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.

Thanks!

Your second line of code:

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

Your second line of code is incorrect and won't return any results.

I'll try to explain this in a way that makes clear sense. I'll number it in order so it's a bit easier to follow, and go step by step and explain every step just to make things clear, hopefully that helps.

1) So the REPLACE function is defined as: REPLACE(state, <target>, <replacement>).

2) So above (in your second line of code) you are looking in the column review.

3) The target you are looking to replace is "public relations".

4) You want to replace it (the public relations) with PR.

The problem:

1) In the target you have "public relations" so the code is going through the column review and looking for matches to "public relations", and then replacing it with "PR". It is then checked to see if it matches the value after the equality operator. i.e. = "PR".

2) The first row entry for the column review is "Basically Aliens but with a better public relations manager." There is a match and "public relations" is replaced with "PR", so you get "Basically Aliens but with a better PR manager.". But when it is checked to see if it matches the value after the equality operator it fails i.e. = "PR", they are not the same. So the original text "Basically Aliens but with a better public relations manager." is not returned.

Here are some further examples that illustrate this that work (they return the original text "Basically Aliens but with a better public relations manager."), that I made up, and I hope it makes sense why:

SELECT * FROM reviews WHERE REPLACE(review, "public relations", "PR") = "Basically Aliens but with a better PR manager.";

SELECT * FROM reviews WHERE REPLACE(review, "Basically Aliens but with a better public relations manager.", "PR") = "PR";

IMPORTANT (SIDE NOTE)

Be careful of = "PR" at the end of the code. If you look at Andrew's previous video and look at the Questions section, they really explain it in a simple way. If you look at the question "I don't understand the SQL REPLACE() function", the reply by Steven Parker really explains it in a simple way. I found his answer really easy to understand. If you still do not understand it, let me know and I will try to explain it. But I'd definitely recommend to check out all the replies by everyone, and see if they help.

I hope this helps and makes sense.