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 orange sky
.a{fill-rule:evenodd;}techdegree
orange sky
Front End Web Development Techdegree Student 4,945 Points

Replace()

Hello!

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

I understand that we are looking for all the instances of 'California' in the state column and we are replacing them with 'CA', but why are we setting the replace() equal to CA (REPLACE(state, "California", "CA") = "CA";)?

thanks

2 Answers

Oliver Duncan
Oliver Duncan
16,642 Points
Oliver Duncan
Oliver Duncan
16,642 Points

If we didn't use the replace() function, the query would read:

SELECT * FROM addresses WHERE state = 'CA';

But since some of the states are listed without the abbreviation, we replace all instances of 'California' with 'CA', then look for instances of 'CA':

SELECT * FROM addresses WHERE REPLACE(state, 'California', 'CA') = 'CA';
Jason Anello
PLUS
Jason Anello
Courses Plus Student 94,610 Points
Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

Hi orange sky,

If I understand your question, the single = in sql is not an assignment like it would be in programming languages like javascript.

It's more like a double equals in those languages. You're doing an equality comparison.

It's saying, after replacing all instances of "California" with "CA", retrieve all the rows where the state value is equal to "CA"

It's not setting the REPLACE to "CA"

Charlie Harcourt
Charlie Harcourt
8,046 Points

But yet even in the video we have all instances of CA but yet there are still states that read California so why is that? Even in the video there are 3 states that show up as California instead of CA.