Day 93/100 – New Unicode String Functions in ClickHouse® 26.3: Better Text Search and Normalization

Introduction

Modern data platforms process enormous volumes of text originating from users all over the world. Names, addresses, product descriptions, customer reviews, search queries, and application logs often contain accented characters, Unicode symbols, ligatures, emojis, and language-specific casing rules. While these characters make text accurate and meaningful for users, they also introduce challenges when performing searches, comparisons, joins, deduplication, or analytics.

Consider a few common examples:

• résumé and resume
• Straße and STRASSE
• crème brûlée and creme brulee
• Different Unicode representations of visually identical characters

Although these strings may look identical—or nearly identical—to people, they are stored differently internally. Traditional string comparison functions such as lower() or upper() often fail to recognize these differences, leading to missed search results, duplicate records, inconsistent grouping, and unexpected join failures.

To simplify Unicode-aware text processing, ClickHouse® 26.3 introduces three powerful new string functions:

• caseFoldUTF8()
• removeDiacriticsUTF8()
• normalizeUTF8NFKCCasefold()

Together, these functions make it much easier to build multilingual applications that perform reliable searches, comparisons, and text normalization while following Unicode standards.

In this article, we'll explore each function in detail, understand when to use it, and walk through practical SQL examples.

---

Why Unicode Normalization Matters

Working with Unicode text is more complicated than it first appears.

Many characters have multiple valid Unicode representations. Two strings can look identical on screen while having completely different binary representations.

For example:

• The letter é can exist as a single Unicode character.
• It can also be represented as the character e followed by a combining accent.

Similarly:

• Straße
• STRASSE

represent the same German word, but traditional lowercase conversion treats them differently because the German letter ß is not equivalent to ss under simple lowercase rules.

Accent marks introduce similar problems:

• résumé
• resume

Both words may represent the same search intent, but standard string comparisons treat them as different values.

Without Unicode normalization:

• Searches miss valid matches.
• Duplicate detection becomes unreliable.
• GROUP BY operations produce unexpected results.
• JOIN conditions fail.
• User-entered data becomes inconsistent across applications.

Unicode-aware normalization solves these problems by transforming text into standardized forms before comparison.

---

Unicode Support Before ClickHouse® 26.3

ClickHouse® already provided several Unicode normalization functions before version 26.3.

Function	Description
normalizeUTF8NFC()	NFC normalization
normalizeUTF8NFD()	NFD normalization
normalizeUTF8NFKC()	Compatibility composition
normalizeUTF8NFKD()	Compatibility decomposition
upperUTF8()	UTF-8 uppercase conversion
lowerUTF8()	UTF-8 lowercase conversion

These functions remain useful for Unicode normalization, but ClickHouse® 26.3 expands the toolkit with three new functions specifically designed for modern multilingual text processing.

---

New Functions Introduced in ClickHouse® 26.3

1. caseFoldUTF8()

What does it do?

caseFoldUTF8() performs Unicode case folding.

Unlike lowerUTF8(), case folding is specifically designed for case-insensitive comparison according to the Unicode standard.

Instead of simply converting uppercase letters into lowercase letters, case folding also handles special Unicode characters that ordinary lowercase conversion cannot.

---

Example

SELECT
    caseFoldUTF8('Straße') AS value1,
    caseFoldUTF8('STRASSE') AS value2;

Output

value1	value2
strasse	strasse

Now compare them:

SELECT
    caseFoldUTF8('Straße') =
    caseFoldUTF8('STRASSE') AS is_equal;

Output

is_equal
1

Notice the difference.

Using lowerUTF8():

Straße
↓

straße

while

STRASSE
↓

strasse

These values are still different.

Unicode case folding correctly transforms:

ß → ss

making both strings identical.

---

Practical Example – Case-Insensitive Name Search

SELECT
    user_id,
    name,
    country
FROM default.users
WHERE caseFoldUTF8(name) =
      caseFoldUTF8('müller');

This query successfully matches:

• Müller
• MÜLLER
• MüLLeR
• müller

regardless of how users entered the name.

---

More Examples

SELECT
    caseFoldUTF8('Hello World') AS english,
    caseFoldUTF8('HÉLLO WÖRLD') AS accented,
    caseFoldUTF8('Ω') AS greek;

Output

english	accented	greek
hello world	héllo wörld	ω

---

2. removeDiacriticsUTF8()

What does it do?

Many European languages use accent marks known as diacritics.

For example:

• é
• ü
• ñ
• å
• ç

Although visually important, many search systems ignore these marks so users can find results without typing accents.

removeDiacriticsUTF8() removes these accent marks while preserving the base letters.

---

Example

SELECT
    removeDiacriticsUTF8('crème brûlée') AS result;

Output

result
creme brulee

---

More Examples

SELECT
    removeDiacriticsUTF8('résumé') AS example1,
    removeDiacriticsUTF8('naïve') AS example2,
    removeDiacriticsUTF8('São Paulo') AS example3,
    removeDiacriticsUTF8('Zürich') AS example4;

Output

example1	example2	example3	example4
resume	naive	Sao Paulo	Zurich

---

Practical Example – Accent-Insensitive Search

SELECT
    city,
    country,
    population
FROM default.cities
WHERE
removeDiacriticsUTF8(lowerUTF8(city))
=
removeDiacriticsUTF8(lowerUTF8('sao paulo'));

This matches:

• São Paulo
• Sao Paulo
• SAO PAULO
• são paulo

without requiring multiple conditions.

---

3. normalizeUTF8NFKCCasefold()

What does it do?

normalizeUTF8NFKCCasefold() combines two powerful Unicode operations into one function:

1. Unicode Compatibility Normalization (NFKC)
2. Unicode Case Folding

It is the most comprehensive Unicode normalization function introduced in ClickHouse® 26.3.

Unlike removeDiacriticsUTF8(), this function does not remove accents. Instead, it standardizes compatibility characters and applies Unicode-aware case folding.

---

What is NFKC Normalization?

Unicode includes many compatibility characters that look different but represent the same logical character.

Examples include:

Character	Normalized Result
fi	fi
Ａ	A
²	2

NFKC converts these compatibility characters into their standard equivalents.

---

Example

SELECT
normalizeUTF8NFKCCasefold('file') AS normalized;

Output

normalized
file

The ligature fi becomes the letters fi, and case folding is also applied.

---

More Examples

SELECT
normalizeUTF8NFKCCasefold('file') AS ligature,
normalizeUTF8NFKCCasefold('Ａ Ｂ Ｃ') AS fullwidth,
normalizeUTF8NFKCCasefold('²²') AS superscript,
normalizeUTF8NFKCCasefold('Straße') AS german;

Output

ligature	fullwidth	superscript	german
file	a b c	22	strasse

---

Practical Example – Normalizing User Search Queries

SELECT
    user_id,
    normalizeUTF8NFKCCasefold(search_query) AS normalized_query
FROM default.search_logs
WHERE normalizeUTF8NFKCCasefold(search_query)
      =
      normalizeUTF8NFKCCasefold('café')
LIMIT 10;

This query ensures that searches become consistent even when users type different Unicode compatibility characters or different letter cases.

If you also want accent-insensitive matching, combine it with removeDiacriticsUTF8().

---

Which Function Should You Use?

Function	Primary Purpose	Typical Use Case
caseFoldUTF8()	Unicode-aware case comparison	Case-insensitive matching
removeDiacriticsUTF8()	Removes accents	Accent-insensitive search
normalizeUTF8NFKCCasefold()	Compatibility normalization + case folding	Deduplication and equality checks
removeDiacriticsUTF8(caseFoldUTF8())	Case + accent normalization	Flexible multilingual search

---

Also New in ClickHouse® 26.3: naturalSortKey()

Although not a Unicode normalization function, ClickHouse® 26.3 also introduces naturalSortKey(), which provides human-friendly sorting for strings containing numbers.

Example:

SELECT name
FROM files
ORDER BY naturalSortKey(name);

Output

name
file1.txt
file2.txt
file10.txt
file20.txt
file100.txt

Without naturalSortKey(), standard lexicographical sorting would incorrectly place file10.txt before file2.txt.

Natural sorting recognizes numeric values inside strings and orders them the way people naturally expect.

---

Quick Reference

Function	Example Input	Output
caseFoldUTF8()	Straße	strasse
caseFoldUTF8()	MÜLLER	müller
removeDiacriticsUTF8()	crème brûlée	creme brulee
removeDiacriticsUTF8()	São Paulo	Sao Paulo
normalizeUTF8NFKCCasefold()	file	file
normalizeUTF8NFKCCasefold()	Straße	strasse
naturalSortKey()	file10.txt	Sorts after file2.txt

---

Best Practices

• Use caseFoldUTF8() instead of lowerUTF8() whenever you need reliable Unicode-aware case-insensitive comparisons.
• Use removeDiacriticsUTF8() for search functionality where accents should not affect matching, such as names, cities, or product catalogs.
• Combine removeDiacriticsUTF8(caseFoldUTF8(column)) to support both case-insensitive and accent-insensitive searches.
• Use normalizeUTF8NFKCCasefold() when importing data from external systems that may contain compatibility characters, ligatures, or full-width Unicode characters.
• Store normalized values as MATERIALIZED columns whenever possible so normalization occurs only once during inserts rather than on every query.
• Choose the least aggressive normalization function that satisfies your use case to avoid altering text more than necessary.

---

Final Thoughts

As applications become increasingly global, handling multilingual text correctly is no longer optional. Reliable search, accurate deduplication, and consistent data processing all depend on understanding Unicode beyond simple uppercase and lowercase conversions.

The new Unicode string functions introduced in ClickHouse® 26.3—caseFoldUTF8(), removeDiacriticsUTF8(), and normalizeUTF8NFKCCasefold()—provide a modern toolkit for working with international text. Whether you're processing customer names, addresses, search queries, product catalogs, or user-generated content, these functions help ensure your comparisons behave consistently across languages and writing systems.

Combined with existing Unicode normalization functions and new utilities like naturalSortKey(), ClickHouse® continues to strengthen its support for real-world text processing while maintaining the high performance expected from a modern analytical database.

As multilingual datasets continue to grow, incorporating these Unicode-aware functions into your data pipelines and queries will help improve search accuracy, reduce duplicate records, and deliver a better experience for users around the world.