Sometimes we have to apply simple operations in multiple columns and modify the stored/original value of those columns. ClickHouse has SELECT modifiers that can help us: {% code-line %}APPLY{% code-line-end %}, {% code-line %}EXCEPT{% code-line-end %} AND {% code-line %}REPLACE{% code-line-end %}.

Let’s see some easy examples using this simple set:

The first one {% code-line %}APPLY{% code-line-end %} will apply a function to all the columns we specify in the SELECT clause like:

Now, with {% code-line %}EXCEPT{% code-line-end %} we can select different columns except the ones specified, and we can combine the modifiers:

The last one is {% code-line %}REPLACE{% code-line-end %} that you can use again apply simple visual functions and replace columns with new values, using alias expressions:

Sweet right? well as we said you can mix them all like this:

or this,

And stay tuned for the next ClickHouse release 21.10 in which we can use a lambda function inside an {% code-line %}APPLY{% code-line-end %}.