Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Reporting with SQL!
You have completed Reporting with SQL!
Preview
Replacing parts of text is handy for privacy concerns, standardization or improving output.
To replace piece of strings of text in a larger body of text you can use the REPLACE() function.
SELECT REPLACE(<original value or column>, <target string>, <replacement string>) FROM <table>;
Cheat Sheets
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Another handy thing you can do is replace
portions of text with other text values.
0:00
This could be good for
adding styling to a web page.
0:05
For example, you could highlight
the search term in search results
0:08
by adding code so
it appears bold in a web browser.
0:12
Or for privacy reasons you might want
to obfuscate e-mail addresses or
0:16
even help search for
non-standardized user input.
0:20
For example, if a user types in
their country code abbreviation
0:23
rather than their full country name.
0:27
Let's take a look at the replace function
now and an example on how to use it.
0:29
Here's the replace function.
0:36
Like the substring function, the replace
function takes more than one value.
0:37
The first value is the column of
value that you want to replace.
0:43
You may want to replace either a part or
all of the text in that column.
0:48
Then the target string.
0:53
This is what you want to replace.
0:55
Finally, the string you want
to replace the target with.
0:57
Let's say in our addresses
table we have a state column
1:01
where the users could have
entered their own value.
1:06
We want to search all addresses
in the state of California.
1:09
For the sake of simplicity,
let's say there's only two values
1:13
that are entered in the state for
California, California and CA.
1:18
Let's replace all instances
of California with CA.
1:23
Now we can use this to search for
all addresses in California.
1:28
When we run this query,
we get all instances of California and
1:33
CA no matter how it was
inserted into the database.
1:38
Again, we're just using
the function in the condition.
1:43
We can use this function over
in the select portion, too.
1:47
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up