Introduction

ClickHouse has three types of functions for string operations. These are:

• General Functions
• Searching Functions
• Replacing Functions

We aimed to explain the most commonly used functions for replacing in strings with examples in this article.

Let’s have a look at the some frequently used replacing functions together.

Replacing Functions:

replaceOne: Replace the first occurrence of substring in a string with given replacement.

• Syntax:

replaceOne(string, substring, replacement)

• Example:

--Replace the first occurrence of Alexa with Siri
SELECT replaceOne('Hello World! My Name is Alexa!', 'Alexa', 'Siri')


┌─replaceOne('Hello World! My Name is Alexa!', 'Alexa', 'Siri')─┐
│ Hello World! My Name is Siri!                                 │
└───────────────────────────────────────────────────────────────┘

replace/replaceAll: Replace all occurrence of substring in a string with given replacement. Syntax is same as “replaceOne“.

• Example:

-- Replace all Alexa with Siri 
SELECT replaceAll('Hello World! My Name is Alexa! Alexa is not Siri', 'Alexa', 'Siri')

┌─replaceAll('Hello World! My Name is Alexa! Alexa is not Siri', 'Alexa', 'Siri')─┐
│ Hello World! My Name is Siri! Siri is not Siri                                  │
└─────────────────────────────────────────────────────────────────────────────────┘


-- Replace all Alexa with Siri
SELECT replace('Hello World! My Name is Alexa! Alexa is not Siri', 'Alexa', 'Siri')

┌─replaceAll('Hello World! My Name is Alexa! Alexa is not Siri', 'Alexa', 'Siri')─┐
│ Hello World! My Name is Siri! Siri is not Siri                                  │
└─────────────────────────────────────────────────────────────────────────────────┘

replaceRegexpOne: Replace the first occurrence of substring matching the regular expression ‘pattern’ in ‘string‘ by the ‘replacement‘ string.”replaceRegexpAll” is same as the “replaceRegexpOne” but replace all occurrence instead.

• Syntax:

replaceRegexpOne(string, pattern, replacement)

Example:

SELECT
    '2022-03-02',
    replaceRegexpOne('2022-03-02', '(\\d{4})-(\\d{2})-(\\d{2})', '\\2/\\3/\\1') AS replaceRegexpOne


┌─'2022-03-02'─┬─replaceRegexpOne─┐
│ 2022-03-02   │ 03/02/2022       │
└──────────────┴──────────────────┘

For detailed information, please visit the official website of ClickHouse.