1. Home
  2. Knowledge Base
  3. ClickHouse
  4. ClickHouse String Functions : Part II
  1. Home
  2. Knowledge Base
  3. ClickHouse Support
  4. ClickHouse String Functions : Part II

ClickHouse String Functions : Part II

Introduction

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

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.

Was this article helpful?

Related Articles

CHISTADATA IS COMMITTED TO OPEN SOURCE SOFTWARE AND BUILDING HIGH PERFORMANCE COLUMNSTORES

In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.