Have you ever ran into a situation where you needed to replace both ends of a string? Maybe it was tags in a set of XML values or phrases at the beginning and end. I came across that situation recently and was able to show if two statements were needed or if REPLACE could be used against the same column in the same statement. Let’s look at a quick example and see what’s possible.
I’ll start out with a small table of websites:
What if there’s a request to UPDATE the Website values so that only the domain name remains? Can we get rid of the https://www. and the .com/ in one statement?
We can try running the following to REPLACE the text before and after the domain name in one statement:
We don’t have any errors according to SSMS but when we execute, we’ll get an error stating:
Msg 264, Level 16, State 1, Line 12
The column name ‘Website’ is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.
Is all hope lost? Of course not. There’s still a way to update both sides of our domain with one statement.
We can plug in one REPLACE to the other REPLACE like this:
The UPDATE above with the nested REPLACE completes successfully. We can check our data and confirm that our domain names look as expected:
Fly the Nest
If you have multiple replacements needed, know that you can use nested replacements to make changes. It may not be pretty but it’s possible so don’t be surprised to see it.
Thanks for reading!
One thought on “Updating with Nested REPLACE Functions”