ClickHouse String Functions : Part II

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 searching in strings with examples in this article.

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

Searching Functions

position: This function looks for a substring in a string and returns the position of its first occurrence. The starting position is set to 1 by default, but you can change it. By default, the position function is case-sensitive; if you would like to search without case-sensitivity, you need to use the “positionCaseInsensitive” function. It has the same syntax and usage as the “position” function.

• Syntax:

position(string,substring[,starting_position])

• Example:

SELECT
    (
        SELECT position('Hello world !, Welcome to ClickHouse!', '!')
    ) AS position1,
    (
        SELECT position('Hello world !, Welcome to ClickHouse!', '!', 14)
    ) AS position2

Query id: 86f04fd8-c8bd-46ee-ab83-15d52207be61

┌─position1─┬─position2─┐
│        13 │        37 │
└───────────┴───────────┘

In this query first and second occurrence of  “!” is found.

multiSearchAllPositions: It is the same as the “position” function, but it returns an array of positions of substrings instead of one substring.

• Syntax:

multiSearchAllPositions(string, [substring1, substring2, ..., substringn])

• Example:

SELECT multiSearchAllPositions('hello world hello world hello world', ['hello', 'world']) AS positionResult

┌─positionResult─┐
│ [1,7]          │
└────────────────┘

As you can see, it returns the position of first occurrence of  word ‘hello‘ and ‘world‘.

multiSearchAny: Returns 1, if at least one substring matches the string, otherwise zero.

• Syntax

multiSearchAny(string, [substr1, substr1, …, substrn])

• Example

-- In this example none of the substrings found in String, so it returns 0
SELECT multiSearchAny('Hello World! This is multiSearchAny example', ['word1', 'word2']) AS SubStringNotFound
┌─SubStringNotFound─┐
│                 0 │
└───────────────────┘

-- In this example some of the substrings found in String, so it returns 1
SELECT multiSearchAny('Hello World! This is multiSearchAny example', ['word1', 'word2', 'example', 'Hello']) AS SubStringFound
┌─SubStringFound─┐
│              1 │
└────────────────┘

multiSearchFirstIndex: Returns the index i (starting from 1) of the first found substring in the array; if no substring matches the string, it returns zero.

• Syntax:

multiSearchFirstIndex(string, [substr1, substr2, …, substrn])

• Example:

-- The query return 4 because 4 is the index of the word 'the' and this is the only word in the string
SELECT multiSearchFirstIndex('Hello world, welcome to the Matrix!', ['ask', 'go', 'create', 'the']) AS multiSearchFirstIndex


┌─multiSearchFirstIndex─┐
│                     4 │
└───────────────────────┘

like: Checks whether a string matches a simple regular expression. The regular expression can contain the % and _. It is also “NOT LIKE”,”ILIKE” and “NOT ILIKE” function exist from this family.

• Syntax:

like(string, pattern)
--or
string LIKE patern

• Example:

-- with LIKE function, it queried if name ends with letter 'd' or not.
-- it is ended letter 'd' so it returns 'Hello World' from query.
SELECT *
FROM
(
    SELECT 'Hello World' AS name
)
WHERE name LIKE '%d'

┌─name────────┐
│ Hello World │
└─────────────┘

countSubstrings: It return the count of substring on given string. “countSubstringsCaseInsensitive” is same as “countSubstrings” but this function incase-sensitive.

• Syntax:

countSubstrings(string, substr[, start_position])

• Example:

--Counts word 'Hello' in the string
SELECT countSubstrings('Hello Hello Hello World Hello World', 'Hello')

┌─countSubstrings('Hello Hello Hello World Hello World', 'Hello')─┐
│                                                               4 │
└─────────────────────────────────────────────────────────────────┘

In this article, we would like to talk about some main search in string functions. For detailed information, please visit the official page for the search functions.