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

Giuseppe Ardito
Giuseppe Ardito
14,130 Points
Posted by Giuseppe Ardito
Giuseppe Ardito
14,130 Points

Replacement functions with multiple conditions

Hi all!

Does the REPLACE() function support multiple conditions - multiple targets and multiple values to replace - within the same column?

What's the best way to replacement multiple bit of a string in a column, selecting the column only one time?

Thanks

1 Answer

Shadd Anderson
.a{fill-rule:evenodd;}techdegree seal-36
Shadd Anderson
Treehouse Project Reviewer

Hello,

Do you mean REPLACE()? :)

Yes, you can do this, but you'll have to nest it, for instance:

REPLACE(REPLACE(REPLACE(columnname, 'string1', 'string4'),'string2', 'string5'),'string3','string6')))

Giuseppe Ardito
Giuseppe Ardito
14,130 Points
Giuseppe Ardito
Giuseppe Ardito
14,130 Points

Yes REPLACE()!
I rectified the question so it should be correct. Thanks for clarifying!

So in the nested REPLACE() the "column" parameter can be omitted?

Steven Parker
Steven Parker
231,198 Points
Steven Parker
Steven Parker
231,198 Points

The "column" parameter cannot be omitted, but you can substitute a string or a function that returns a string for a column name.

You'll notice in Shadd's nested example that the inner-most function has a column for the first argument, but the others all have the next nested "replace" function as the first argument.

But you don't need to use nesting if you only want to replace multiple instances of the same string. For example:

SELECT REPLACE('This is my fist.', 'is', 'xx');   -->  Thxx xx my fxxt.