Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Reporting with SQL

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Replacing Portions of Text

1:58 with Andrew Chalkley

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 up

    Related 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 up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up