---
title: "ClickHouse® Tips #12: Apply Functions to Columns with a Single Call"
excerpt: "Clickhouse® 21 allows some fancy operations packed into multiple columns with SELECT modifiers."
authors: "Tinybird"
categories: "The Data Base"
createdOn: "2023-09-25 00:00:00"
publishedOn: "2021-09-29 00:00:00"
updatedOn: "2022-09-08 00:00:00"
status: "published"
---

<p>Sometimes we have to apply simple operations in multiple columns and modify the stored/original value of those columns. ClickHouse® has SELECT <a href="https://clickhouse.com/docs/en/sql-reference/statements/select/#select-modifiers">modifiers</a> that can help us: <code>APPLY</code>, <code>EXCEPT</code> AND <code>REPLACE</code>.</p><p>Let’s see some easy examples using this simple set:</p><figure class="kg-card kg-embed-card"><iframe loading="lazy" src="https://snippets.tinybird.co/endpoint?code=CREATE%20TABLE%20columns_transformers%20%28i%20Int64%2C%20j%20Int16%2C%20k%20Int64%29%20ENGINE%20%3D%20MergeTree%20ORDER%20by%20%28i%29%3B%0AINSERT%20INTO%20columns_transformers%20VALUES%20%28100%2C%2010%2C%20324%29%2C%20%28120%2C%208%2C%2023%29%2C%20%28180%2C%202%2C%2078%29%3B%0A%0A&amp;language=sql&amp;title=Create%20dataset&amp;run=&amp;token=" width="100%"></iframe></figure><p>The first one <code>APPLY</code> will apply a function to all the columns we specify in the SELECT clause like:</p><figure class="kg-card kg-embed-card"><iframe loading="lazy" src="https://snippets.tinybird.co/endpoint?code=SELECT%20%2A%20APPLY%28sum%29%20FROM%20columns_transformers%3B%0A%0A%0A%E2%94%8C%E2%94%80sum%28i%29%E2%94%80%E2%94%AC%E2%94%80sum%28j%29%E2%94%80%E2%94%AC%E2%94%80sum%28k%29%E2%94%80%E2%94%90%0A%E2%94%82%20%20%20%20400%20%E2%94%82%20%20%20%20%2020%20%E2%94%82%20%20%20%20425%20%E2%94%82%0A%E2%94%94%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%B4%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%B4%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%98%0A%0A1%20rows%20in%20set.%20Elapsed%3A%200.002%20sec.%0A%0A%0A&amp;language=sql&amp;title=Using%20APPLY&amp;run=&amp;token=" width="100%"></iframe></figure><p>Now, with <code>EXCEPT</code> we can select different columns except the ones specified, and we can combine the modifiers:</p><figure class="kg-card kg-embed-card"><iframe loading="lazy" src="https://snippets.tinybird.co/endpoint?code=SELECT%20%2A%20EXCEPT%20%28i%29%20APPLY%20%28sum%29%0AFROM%20columns_transformers%0A%0A%E2%94%8C%E2%94%80sum%28j%29%E2%94%80%E2%94%AC%E2%94%80sum%28k%29%E2%94%80%E2%94%90%0A%E2%94%82%20%20%20%20%2020%20%E2%94%82%20%20%20%20425%20%E2%94%82%0A%E2%94%94%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%B4%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%98%0A%0A1%20rows%20in%20set.%20Elapsed%3A%200.001%20sec.%20%0A%0A&amp;language=sql&amp;title=Using%20EXCEPT&amp;run=&amp;token=" width="100%"></iframe></figure><p>The last one is <code>REPLACE</code> that you can use again apply simple visual functions and replace columns with new values, using alias expressions:</p><figure class="kg-card kg-embed-card"><iframe loading="lazy" src="https://snippets.tinybird.co/endpoint?code=SELECT%20%2A%20REPLACE%28i%20-%2010%20AS%20i%29%20FROM%20columns_transformers%0A%0A%E2%94%8C%E2%94%80%E2%94%80%E2%94%80i%E2%94%80%E2%94%AC%E2%94%80%E2%94%80j%E2%94%80%E2%94%AC%E2%94%80%E2%94%80%E2%94%80k%E2%94%80%E2%94%90%0A%E2%94%82%20%2090%20%E2%94%82%2010%20%E2%94%82%20324%20%E2%94%82%0A%E2%94%82%20110%20%E2%94%82%20%208%20%E2%94%82%20%2023%20%E2%94%82%0A%E2%94%82%20170%20%E2%94%82%20%202%20%E2%94%82%20%2078%20%E2%94%82%0A%E2%94%94%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%B4%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%B4%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%98%0A%0A3%20rows%20in%20set.%20Elapsed%3A%200.001%20sec.%20%0A%0A&amp;language=sql&amp;title=Using%20REPLACE&amp;run=&amp;token=" width="100%"></iframe></figure><p>Sweet right? well as we said you can mix them all like this:</p><figure class="kg-card kg-embed-card"><iframe loading="lazy" src="https://snippets.tinybird.co/endpoint?code=SELECT%20COLUMNS%28%27%5Bjk%5D%27%29%20APPLY%28toString%29%20APPLY%28length%29%20APPLY%28max%29%20from%20columns_transformers%3B%0A%0A%E2%94%8C%E2%94%80max%28length%28toString%28j%29%29%29%E2%94%80%E2%94%AC%E2%94%80max%28length%28toString%28k%29%29%29%E2%94%80%E2%94%90%0A%E2%94%82%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202%20%E2%94%82%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%203%20%E2%94%82%0A%E2%94%94%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%B4%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%98%0A%0A1%20rows%20in%20set.%20Elapsed%3A%200.002%20sec.%20%0A%0A&amp;language=sql&amp;title=Using%20the%20same%20modifier&amp;run=&amp;token=" width="100%"></iframe></figure><p>or this,</p><figure class="kg-card kg-embed-card"><iframe loading="lazy" src="https://snippets.tinybird.co/endpoint?code=SELECT%20%2A%20REPLACE%28i%20-%2010%20AS%20i%29%20EXCEPT%20%28j%29%20APPLY%28sum%29%20from%20columns_transformers%3B%0A%0A%E2%94%8C%E2%94%80sum%28minus%28i%2C%2010%29%29%E2%94%80%E2%94%AC%E2%94%80sum%28k%29%E2%94%80%E2%94%90%0A%E2%94%82%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20370%20%E2%94%82%20%20%20%20425%20%E2%94%82%0A%E2%94%94%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%B4%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%80%E2%94%98%0A%0A1%20rows%20in%20set.%20Elapsed%3A%200.001%20sec.%20%0A%0A&amp;language=sql&amp;title=Using%20different%20modifiers&amp;run=&amp;token=" width="100%"></iframe></figure><p>And stay tuned for the next ClickHouse® release 21.10 in which we can use a lambda function inside an <code>APPLY</code>.</p>