v.22.1New Feature

Added string manipulation functions and fixed substring error in ClickHouse

Official CH changelog text
Added left, right, leftUTF8, rightUTF8 functions. Fix error in implementation of substringUTF8 function with negative offset (offset from the end of string). #33407 (alexey-milovidov).
Introduced new string functions left, right, leftUTF8, and rightUTF8 for extracting substrings from the beginning or end of strings, along with a fix for the substringUTF8 function to correctly handle negative offsets.

Why it matters

These additions provide more intuitive and efficient ways to extract substrings based on character count from either the start or end of strings, supporting both byte-wise and UTF-8 encodings. The fix for substringUTF8 ensures consistent and correct extraction when using negative offsets to specify positions from the string's end.

How to use it

Use the new functions as follows:

SELECT left(string, length)           -- extracts the first <length> bytes
SELECT right(string, length)          -- extracts the last <length> bytes
SELECT leftUTF8(string, length)       -- extracts the first <length> UTF-8 characters
SELECT rightUTF8(string, length)      -- extracts the last <length> UTF-8 characters


The substringUTF8 function now correctly accepts negative offsets to count from the end of the string:

SELECT substringUTF8(string, -offset, length)

Related resources

PreviousNext