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 Reporting with SQL Working with Text Replacing Portions of Text

Posted by Kristian Woods
Kristian Woods
23,414 Points

I don't understand the SQL REPLACE() function

In the video, he says that:

REPLACE(state, "California", "CA") ;

This function will change every case of the string "California" and replace it with "CA", within the "state" column. I understand this concept.

However, he then goes on to say:

SELECT * FROM addresses WHERE REPLACE(state, "California", "CA") = "CA"

From what I understand, this REPLACE function changes instances of "California" to "CA". Then is checked to see if it matches the value after the equality operator. i.e. = "CA". And because "California" was changed to "CA", you should find your desired results.

However, where I become lost, is when he says that the query will return all results where "California" AND "CA" have been entered as a value into the column... How can it check "California" when we changed all instances of it to "CA"? I thought you'd only receive results where "CA" had been entered. NOT "California"...

Thanks

Derek Lin
Derek Lin
1,563 Points

Wait I still don't quite understand why there would still be columns with values California if we changed all of them . and why when we select them it woudl give us both CA and California. is it that replace in this case isn't changing it but only changing it for reading purposes? so that both those that have CA and those that have California will be retrieved in one go through the replace method?

7 Answers

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

You receive results from both "California" and "CA" because of the REPLACE.

If the column contains "California", the REPLACE changes it into "CA", and then it matches the comparison.

And if the column contains "CA", the REPLACE does not change it, so it still matches the comparison.

So because of the REPLACE combined with the equality test, the query will return all rows where the column contains "California", AND all rows where the column contains "CA".

As a side note, while I agree that REPLACE is very handy, in this particular situation I think it would be clearer and more concise to write the query this way:

SELECT * FROM addresses WHERE state IN ("California", "CA")
Thomas Helms
Thomas Helms
16,816 Points
Thomas Helms
Thomas Helms
16,816 Points

Agreed. The usage of WHERE REPLACE made it rather confusing. I prefer WHERE IN, but your explanation clears it up. Thanks.

Keith Ostertag
Keith Ostertag
16,619 Points
Keith Ostertag
Keith Ostertag
16,619 Points

@Justin Molyneaux - I just want to say "Thank You". Your explanation makes it clear for me.

Justin Molyneaux
Justin Molyneaux
13,329 Points
Justin Molyneaux
Justin Molyneaux
13,329 Points

Glad that I could help you :).

Derek Lin
Derek Lin
1,563 Points
Derek Lin
Derek Lin
1,563 Points

Wait I still don't quite understand why there would still be columns with values California if we changed all of them . and why when we select them it woudl give us both CA and California. is it that replace in this case isn't changing it but only changing it for reading purposes? so that both those that have CA and those that have California will be retrieved in one go through the replace method?

Justin Molyneaux
Justin Molyneaux
13,329 Points
Justin Molyneaux
Justin Molyneaux
13,329 Points

Hey Derek, when you use the REPLACE() you are not actually updating the information in the database. Instead, you are just manipulating it for retrieval and reading purposes. If you recall back in the CRUD lessons, the way to actually change data in the database is to UPDATE the table and then SET the information within a column.

Alejandro Molina
Alejandro Molina
3,997 Points
Alejandro Molina
Alejandro Molina
3,997 Points

It sounds like this was a case of just using a bad example.

Jayanthi Padmanabhan
Jayanthi Padmanabhan
7,882 Points
Jayanthi Padmanabhan
Jayanthi Padmanabhan
7,882 Points

Hi,

In that example, there were total 20 records available and in which 10 records are having the state as "CA" or "California". These are the original data. Our aim to write a query to select the state of California in whichever forms. So REPLACE function used here to replace Californa to CA to compare with the equal operator against the value "CA".(Only in condition and it will not change original data) So As the result, we got 10 records of California or CA.

William Lumanauw
William Lumanauw
5,783 Points
William Lumanauw
William Lumanauw
5,783 Points

Thankyou for your great explanation

Claudio Raichande
Claudio Raichande
1,444 Points
Claudio Raichande
Claudio Raichande
1,444 Points

I think we should just read it like All columns WHERE state = CA, being that California also = CA

elomaur
elomaur
5,276 Points
elomaur
elomaur
5,276 Points

I am still confused on why California is being returned when it states above that

If the column contains "California", the REPLACE changes it into "CA", and then it matches the comparison.

What does changes it into CA mean in this case? Can someone please explain why California is being returned after it has been replaced? Or I'm I missing something crucial in the syntax explanation?