How to Eliminate Ugly Nested REPLACE() Functions

Watch this week's video on YouTube

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

-- Input: Red, Blue, Green
-- Output: RGB
SELECT 
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(c.Colors,'Red','R')
    ,'Green','G')
    ,'Blue','B')
    ,', ','') AS Colors
FROM
    (SELECT 'Red, Green, Blue' AS Colors) c

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

-- Input: Red, Blue, Green
-- Output: RGB
SELECT REPLACE(REPLACE(REPLACE(REPLACE(c.Colors,'Red','R'),'Green','G'),'Blue','B'),', ','') AS Colors
FROM
    (SELECT 'Red, Green, Blue' AS Colors) c

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:

-- Input: Red, Blue, Green
-- Output: RGB
SELECT 
    s.Colors
FROM
    (SELECT 'Red, Green, Blue' AS Colors) c
    CROSS APPLY (SELECT REPLACE(c.Colors,'Red','R') AS Colors) r
    CROSS APPLY (SELECT REPLACE(r.Colors,'Green','G') AS Colors) g
    CROSS APPLY (SELECT REPLACE(g.Colors,'Blue','B') AS Colors) b
    CROSS APPLY (SELECT REPLACE(b.Colors,', ','') AS Colors) s

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.