Functions for working with strings

## endsWithUTF8[​](#endsWithUTF8 "Direct link to endsWithUTF8")

Introduced in: v23.8.0

Returns whether string `s` ends with `suffix`.
Assumes that the string contains valid UTF-8 encoded text.
If this assumption is violated, no exception is thrown and the result is undefined.

**Syntax**

```
endsWithUTF8(s, suffix)
```

**Arguments**

- `s` — String to check. [`String`](/docs/sql-reference/data-types/string)
- `suffix` — Suffix to check for. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `1` if `s` ends with `suffix`, otherwise `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT endsWithUTF8('данных', 'ых');
```

```
┌─endsWithUTF8('данных', 'ых')─┐
│                            1 │
└──────────────────────────────┘
```

## extractTextFromHTML[​](#extractTextFromHTML "Direct link to extractTextFromHTML")

Introduced in: v21.3.0

Extracts text content from HTML or XHTML.

This function removes HTML tags, comments, and script/style elements, leaving only the text content. It handles:

- Removal of all HTML/XML tags
- Removal of comments (`<!-- -->`)
- Removal of script and style elements with their content
- Processing of CDATA sections (copied verbatim)
- Proper whitespace handling and normalization

Note: HTML entities are not decoded and should be processed with a separate function if needed.

**Syntax**

```
extractTextFromHTML(html)
```

**Arguments**

- `html` — String containing HTML content to extract text from. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the extracted text content with normalized whitespace. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT extractTextFromHTML('
<html>
    <head><title>Page Title</title></head>
    <body>
        <p>Hello <b>World</b>!</p>
        <script>alert("test");</script>
        <!-- comment -->
    </body>
</html>
');
```

```
┌─extractTextFromHTML('<html><head>...')─┐
│ Page Title Hello World!                │
└────────────────────────────────────────┘
```

## firstLine[​](#firstLine "Direct link to firstLine")

Introduced in: v23.7.0

Returns the first line of a multi-line string.

**Syntax**

```
firstLine(s)
```

**Arguments**

- `s` — Input string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the first line of the input string or the whole string if there are no line separators. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT firstLine('foo\\nbar\\nbaz')
```

```
┌─firstLine('foo\nbar\nbaz')─┐
│ foo                        │
└────────────────────────────┘
```

## idnaDecode[​](#idnaDecode "Direct link to idnaDecode")

Introduced in: v24.1.0

Returns the Unicode (UTF-8) representation (ToUnicode algorithm) of a domain name according to the [Internationalized Domain Names in Applications](https://en.wikipedia.org/wiki/Internationalized_domain_name#Internationalizing_Domain_Names_in_Applications) (IDNA) mechanism.
In case of an error (e.g. because the input is invalid), the input string is returned.
Note that repeated application of [`idnaEncode()`](#idnaEncode) and [`idnaDecode()`](#idnaDecode) does not necessarily return the original string due to case normalization.

**Syntax**

```
idnaDecode(s)
```

**Arguments**

- `s` — Input string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a Unicode (UTF-8) representation of the input string according to the IDNA mechanism of the input value. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT idnaDecode('xn--strae-oqa.xn--mnchen-3ya.de')
```

```
┌─idnaDecode('xn--strae-oqa.xn--mnchen-3ya.de')─┐
│ straße.münchen.de                             │
└───────────────────────────────────────────────┘
```

## idnaEncode[​](#idnaEncode "Direct link to idnaEncode")

Introduced in: v24.1.0

Returns the ASCII representation (ToASCII algorithm) of a domain name according to the [Internationalized Domain Names in Applications](https://en.wikipedia.org/wiki/Internationalized_domain_name#Internationalizing_Domain_Names_in_Applications) (IDNA) mechanism.
The input string must be UTF-encoded and translatable to an ASCII string, otherwise an exception is thrown.

Note

No percent decoding or trimming of tabs, spaces or control characters is performed.

**Syntax**

```
idnaEncode(s)
```

**Arguments**

- `s` — Input string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns an ASCII representation of the input string according to the IDNA mechanism of the input value. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT idnaEncode('straße.münchen.de')
```

```
┌─idnaEncode('straße.münchen.de')─────┐
│ xn--strae-oqa.xn--mnchen-3ya.de     │
└─────────────────────────────────────┘
```

## initcap[​](#initcap "Direct link to initcap")

Introduced in: v23.7.0

Converts the first letter of each word to upper case and the rest to lower case.
Words are sequences of alphanumeric characters separated by non-alphanumeric characters.

Note

Because `initcap` converts only the first letter of each word to upper case you may observe unexpected behaviour for words containing apostrophes or capital letters.
This is a known behaviour and there are no plans to fix it currently.

**Syntax**

```
initcap(s)
```

**Arguments**

- `s` — Input string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `s` with the first letter of each word converted to upper case. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT initcap('building for fast')
```

```
┌─initcap('building for fast')─┐
│ Building For Fast            │
└──────────────────────────────┘
```

**Example of known behavior for words containing apostrophes or capital letters**

```
SELECT initcap('John''s cat won''t eat.');
```

```
┌─initcap('Joh⋯n\'t eat.')─┐
│ John'S Cat Won'T Eat.    │
└──────────────────────────┘
```

## initcapUTF8[​](#initcapUTF8 "Direct link to initcapUTF8")

Introduced in: v23.7.0

Like [`initcap`](#initcap), `initcapUTF8` converts the first letter of each word to upper case and the rest to lower case.
Assumes that the string contains valid UTF-8 encoded text.
If this assumption is violated, no exception is thrown and the result is undefined.

Note

This function does not detect the language, e.g. for Turkish the result might not be exactly correct (i/İ vs. i/I).
If the length of the UTF-8 byte sequence is different for upper and lower case of a code point, the result may be incorrect for this code point.

**Syntax**

```
initcapUTF8(s)
```

**Arguments**

- `s` — Input string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `s` with the first letter of each word converted to upper case. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT initcapUTF8('не тормозит')
```

```
┌─initcapUTF8('не тормозит')─┐
│ Не Тормозит                │
└────────────────────────────┘
```

## isValidASCII[​](#isValidASCII "Direct link to isValidASCII")

Introduced in: v25.9.0

Returns 1 if the input String or FixedString contains only ASCII bytes (0x00–0x7F), otherwise 0. Optimized for the positive case (the input *is* valid ASCII).

**Syntax**

```
isValidASCII(str)
```

**Aliases**: `isASCII`

**Arguments**

- None.

**Returned value**

**Examples**

**isValidASCII**

```
SELECT isValidASCII('hello') AS is_ascii, isValidASCII('你好') AS is_not_ascii
```

## isValidUTF8[​](#isValidUTF8 "Direct link to isValidUTF8")

Introduced in: v20.1.0

Checks if the set of bytes constitutes valid UTF-8-encoded text.

**Syntax**

```
isValidUTF8(s)
```

**Arguments**

- `s` — The string to check for UTF-8 encoded validity. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `1`, if the set of bytes constitutes valid UTF-8-encoded text, otherwise `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**