Functions for working with strings

```
stringJaccardIndexUTF8(s1, s2)
```

**Arguments**

- `s1` — First input UTF8 string. [`String`](/docs/sql-reference/data-types/string)
- `s2` — Second input UTF8 string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the Jaccard similarity index between the two UTF8 strings. [`Float64`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

```
SELECT stringJaccardIndexUTF8('我爱你', '我也爱你')
```

```
┌─stringJaccardIndexUTF8('我爱你', '我也爱你')─┐
│                                       0.75 │
└─────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Returns the substring of a string `s` which starts at the specified byte index `offset`.
Byte counting starts from 1 with the following logic:

- If `offset` is `0`, an empty string is returned.
- If `offset` is negative, the substring starts `offset` characters from the end of the string, rather than from the beginning.

An optional argument `length` specifies the maximum number of bytes the returned substring may have.

**Syntax**

```
substring(s, offset[, length])
```

**Aliases**: `byteSlice`, `mid`, `substr`

**Arguments**

- `s` — The string to calculate a substring from. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring) or [`Enum`](/docs/sql-reference/data-types/enum)
- `offset` — The starting position of the substring in `s`. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)
- `length` — Optional. The maximum length of the substring. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a substring of `s` with `length` many bytes, starting at index `offset`. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic usage**

```
SELECT 'database' AS db, substr(db, 5), substr(db, 5, 1)
```

```
┌─db───────┬─substring('database', 5)─┬─substring('database', 5, 1)─┐
│ database │ base                     │ b                           │
└──────────┴──────────────────────────┴─────────────────────────────┘
```

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

Introduced in: v23.7.0

Returns the substring of `s` before `count` occurrences of the delimiter `delim`, as in Spark or MySQL.

**Syntax**

```
substringIndex(s, delim, count)
```

**Aliases**: `SUBSTRING_INDEX`

**Arguments**

- `s` — The string to extract substring from. [`String`](/docs/sql-reference/data-types/string)
- `delim` — The character to split. [`String`](/docs/sql-reference/data-types/string)
- `count` — The number of occurrences of the delimiter to count before extracting the substring. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. [`UInt`](/docs/sql-reference/data-types/int-uint) or [`Int`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a substring of `s` before `count` occurrences of `delim`. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT substringIndex('www.clickhouse.com', '.', 2)
```

```
┌─substringIndex('www.clickhouse.com', '.', 2)─┐
│ www.clickhouse                               │
└──────────────────────────────────────────────┘
```

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

Introduced in: v23.7.0

Returns the substring of `s` before `count` occurrences of the delimiter `delim`, specifically for Unicode code points.
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**

```
substringIndexUTF8(s, delim, count)
```

**Arguments**

- `s` — The string to extract substring from. [`String`](/docs/sql-reference/data-types/string)
- `delim` — The character to split. [`String`](/docs/sql-reference/data-types/string)
- `count` — The number of occurrences of the delimiter to count before extracting the substring. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. [`UInt`](/docs/sql-reference/data-types/int-uint) or [`Int`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a substring of `s` before `count` occurrences of `delim`. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**UTF8 example**

```
SELECT substringIndexUTF8('www.straßen-in-europa.de', '.', 2)
```

```
www.straßen-in-europa
```

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

Introduced in: v1.1.0

Returns the substring of a string `s` which starts at the specified code point index `offset`.
Code point counting starts from `1` with the following logic:

- If `offset` is `0`, an empty string is returned.
- If `offset` is negative, the substring starts `offset` code points from the end of the string, rather than from the beginning.

An optional argument `length` specifies the maximum number of code points the returned substring may have.

Note

This function 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**

```
substringUTF8(s, offset[, length])
```

**Arguments**

- `s` — The string to calculate a substring from. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring) or [`Enum`](/docs/sql-reference/data-types/enum)
- `offset` — The starting position of the substring in `s`. [`Int`](/docs/sql-reference/data-types/int-uint) or [`UInt`](/docs/sql-reference/data-types/int-uint)
- `length` — The maximum length of the substring. Optional. [`Int`](/docs/sql-reference/data-types/int-uint) or [`UInt`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a substring of `s` with `length` many code points, starting at code point index `offset`. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT 'Täglich grüßt das Murmeltier.' AS str, substringUTF8(str, 9), substringUTF8(str, 9, 5)
```

```
Täglich grüßt das Murmeltier.    grüßt das Murmeltier.    grüßt
```

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

Introduced in: v20.1.0

Converts a string to valid UTF-8 encoding by replacing any invalid UTF-8 characters with the replacement character `�` (U+FFFD).
When multiple consecutive invalid characters are found, they are collapsed into a single replacement character.

**Syntax**

```
toValidUTF8(s)
```

**Arguments**

- `s` — Any set of bytes represented as the String data type object. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a valid UTF-8 string. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT toValidUTF8('\\x61\\xF0\\x80\\x80\\x80b')
```

```
c
┌─toValidUTF8('a����b')─┐
│ a�b                   │
└───────────────────────┘
```

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

Introduced in: v20.1.0

Removes the specified characters from the start and end of a string.
By default, removes common whitespace (ASCII) characters.

**Syntax**

```
trimBoth(s[, trim_characters])
```

**Aliases**: `trim`

**Arguments**

- `s` — String to trim. [`String`](/docs/sql-reference/data-types/string)
- `trim_characters` — Optional. Characters to trim. If not specified, common whitespace characters are removed. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the string with specified characters trimmed from both ends. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT trimBoth('$$ClickHouse$$', '$')
```

```
┌─trimBoth('$$⋯se$$', '$')─┐
│ ClickHouse               │
└──────────────────────────┘
```

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

Introduced in: v20.1.0