String Functions

upper

Converts the ASCII Latin symbols in a string to uppercase.

Alias

ucase

Example

SELECT upper('a')

┌─upper('a')─┐
│ A          │
└────────────┘

lower

Converts the ASCII Latin symbols in a string to lowercase.

Alias

lcase

Example

SELECT lower('A')

┌─lower('A')─┐
│ a          │
└────────────┘

substring(s, offset, length)

Returns a substring with length many bytes, starting at the byte at index offset. Character indexing starts from 1.

substring(s, offset, length)

Alias

• substr
• mid

Example

SELECT substring('www.clickhouse.com', 5, 10)

┌─substring('www.clickhouse.com', 5, 10)─┐
│clickhouse                              │
└────────────────────────────────────────┘

trim()

Removes the specified characters from the start or end of a string. If not specified otherwise, the function removes whitespace (ASCII-character 32).

trim([[LEADING|TRAILING|BOTH] trim_character FROM] input_string)

Arguments

• trim_character — Specified characters for trim. String.
• input_string — String for trim. String.

Returned value

• A string without leading and/or trailing specified characters.
• Type: String.

Example

SELECT trim(BOTH ' ()' FROM '( Hello, world! )');

┌─replaceRegexpAll('( Hello, world! )', concat('^[', regexpQuoteMeta(' ()'), ']+|[', regexpQuoteMeta(' ()'), ']+$'), '')─┐
│ Hello, world!                                                                                                          │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Length

Returns the length of a string in bytes (not: in characters or Unicode code points). The function also works for arrays.

length(s)

Arguments

• s — Specified characters for getting length. String.

Alias

OCTET_LENGTH