How to Eliminate Ugly Nested REPLACE() Functions

SQL in 60 Seconds #4

On white: Who you really are” by James Jordan is licensed under CC BY-ND 2.0

Prefer video? Watch this content on my YouTube channel!

How many times have you had to transform some column value and ended up stacking several nested SQL REPLACE() functions like this?

Ugly right? And that’s after careful formatting to try and make it look readable. I could have just left it as:

Here we only have 4 nested REPLACE functions. My shameful record is 29. I’m not proud of it, but sometimes it’s the only way to get things done.

Not only are these nested REPLACE() functions difficult to write, but they are difficult to read too.

Instead of suffering through all of that ugly nesting, what you can do instead is use CROSS APPLY:

Technically the CROSS APPLY solution uses more characters. But it is infinitely more readable.

And the server? The server doesn’t care about the additional characters —it still compiles it down to the same 1s and 0s:

So next time you have to nest several REPLACE() functions (or any other string functions), do yourself a favor and make it more readable by using CROSS APPLY instead. Your future self will thank you.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!