Functions for working with strings

**Examples**

**Usage example**

```
SELECT compareSubstrings('Saxony', 'Anglo-Saxon', 0, 6, 5) AS result
```

```
┌─result─┐
│      0 │
└────────┘
```

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

Introduced in: v1.1.0

Concatenates the given arguments.

Arguments which are not of types [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring) are converted to strings using their default serialization.
As this decreases performance, it is not recommended to use non-String/FixedString arguments.

**Syntax**

```
concat([s1, s2, ...])
```

**Arguments**

- `s1, s2, ...` — Any number of values of arbitrary type. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the String created by concatenating the arguments. If any of arguments is `NULL`, the function returns `NULL`. If there are no arguments, it returns an empty string. [`Nullable(String)`](/docs/sql-reference/data-types/nullable)

**Examples**

**String concatenation**

```
SELECT concat('Hello, ', 'World!')
```

```
┌─concat('Hello, ', 'World!')─┐
│ Hello, World!               │
└─────────────────────────────┘
```

**Number concatenation**

```
SELECT concat(42, 144)
```

```
┌─concat(42, 144)─┐
│ 42144           │
└─────────────────┘
```

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

Introduced in: v1.1.0

Like [`concat`](#concat) but assumes that `concat(s1, s2, ...) → sn` is injective,
i.e, it returns different results for different arguments.

Can be used for optimization of `GROUP BY`.

**Syntax**

```
concatAssumeInjective([s1, s2, ...])
```

**Arguments**

- `s1, s2, ...` — Any number of values of arbitrary type. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)

**Returned value**

Returns the string created by concatenating the arguments. If any of argument values is `NULL`, the function returns `NULL`. If no arguments are passed, it returns an empty string. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Group by optimization**

```
SELECT concat(key1, key2), sum(value) FROM key_val GROUP BY concatAssumeInjective(key1, key2)
```

```
┌─concat(key1, key2)─┬─sum(value)─┐
│ Hello, World!      │          3 │
│ Hello, World!      │          2 │
│ Hello, World       │          3 │
└────────────────────┴────────────┘
```

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

Introduced in: v22.12.0

Concatenates the provided strings, separating them by the specified separator.

**Syntax**

```
concatWithSeparator(sep[, exp1, exp2, ...])
```

**Aliases**: `concat_ws`

**Arguments**

- `sep` — The separator to use. [`const String`](/docs/sql-reference/data-types/string) or [`const FixedString`](/docs/sql-reference/data-types/fixedstring)
- `exp1, exp2, ...` — Expression to be concatenated. Arguments which are not of type `String` or `FixedString` are converted to strings using their default serialization. As this decreases performance, it is not recommended to use non-String/FixedString arguments. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the String created by concatenating the arguments. If any of the argument values is `NULL`, the function returns `NULL`. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT concatWithSeparator('a', '1', '2', '3', '4')
```

```
┌─concatWithSeparator('a', '1', '2', '3', '4')─┐
│ 1a2a3a4                                      │
└──────────────────────────────────────────────┘
```

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

Introduced in: v22.12.0

Like [`concatWithSeparator`](#concatWithSeparator) but assumes that `concatWithSeparator(sep[,exp1, exp2, ... ]) → result` is injective.
A function is called injective if it returns different results for different arguments.

Can be used for optimization of `GROUP BY`.

**Syntax**

```
concatWithSeparatorAssumeInjective(sep[, exp1, exp2, ... ])
```

**Arguments**

- `sep` — The separator to use. [`const String`](/docs/sql-reference/data-types/string) or [`const FixedString`](/docs/sql-reference/data-types/fixedstring)
- `exp1, exp2, ...` — Expression to be concatenated. Arguments which are not of type `String` or `FixedString` are converted to strings using their default serialization. As this decreases performance, it is not recommended to use non-String/FixedString arguments. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)

**Returned value**

Returns the String created by concatenating the arguments. If any of the argument values is `NULL`, the function returns `NULL`. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
CREATE TABLE user_data (
user_id UInt32,
first_name String,
last_name String,
score UInt32
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO user_data VALUES
(1, 'John', 'Doe', 100),
(2, 'Jane', 'Smith', 150),
(3, 'John', 'Wilson', 120),
(4, 'Jane', 'Smith', 90);

SELECT
    concatWithSeparatorAssumeInjective('-', first_name, last_name) as full_name,
    sum(score) as total_score
FROM user_data
GROUP BY concatWithSeparatorAssumeInjective('-', first_name, last_name);
```

```
┌─full_name───┬─total_score─┐
│ Jane-Smith  │         240 │
│ John-Doe    │         100 │
│ John-Wilson │         120 │
└─────────────┴─────────────┘
```

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

Introduced in: v25.10.0

Converts numbers between different number bases.

The function converts a number from one base to another. It supports bases from 2 to 36.
For bases higher than 10, letters A-Z (case insensitive) are used to represent digits 10-35.

This function is compatible with MySQL's CONV() function.

**Syntax**

```
conv(number, from_base, to_base)
```

**Arguments**

- `number` — The number to convert. Can be a string or numeric type. - `from_base` — The source base (2-36). Must be an integer. - `to_base` — The target base (2-36). Must be an integer.

**Returned value**

String representation of the number in the target base.

**Examples**

**Convert decimal to binary**

```
SELECT conv('10', 10, 2)
```

```
1010
```

**Convert hexadecimal to decimal**

```
SELECT conv('FF', 16, 10)
```

```
255
```

**Convert with negative number**

```
SELECT conv('-1', 10, 16)
```

```
FFFFFFFFFFFFFFFF
```

**Convert binary to octal**

```
SELECT conv('1010', 2, 8)
```

```
12
```

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

Introduced in: v1.1.0

Returns string `s` converted from the encoding `from` to encoding `to`.

**Syntax**

```
convertCharset(s, from, to)
```

**Arguments**

- `s` — Input string. [`String`](/docs/sql-reference/data-types/string)
- `from` — Source character encoding. [`String`](/docs/sql-reference/data-types/string)
- `to` — Target character encoding. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns string `s` converted from encoding `from` to encoding `to`. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT convertCharset('Café', 'UTF-8', 'ISO-8859-1');
```

```
┌─convertChars⋯SO-8859-1')─┐
│ Caf�                     │
└──────────────────────────┘
```

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

Introduced in: v24.1.0

Calculates the [Damerau-Levenshtein distance](https://en.wikipedia.org/wiki/Damerau%E2%80%93Levenshtein_distance) between two byte strings.

**Syntax**

```
damerauLevenshteinDistance(s1, s2)
```

**Arguments**

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

**Returned value**

Returns the Damerau-Levenshtein distance between the two strings. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT damerauLevenshteinDistance('clickhouse', 'mouse')
```