String Functions
This page provides a comprehensive overview of String functions in Databend, organized by functionality for easy reference.
String Concatenation and Manipulation
| Function | Description | Example |
|---|---|---|
| CONCAT | Concatenates strings | CONCAT('data', 'bend') → 'databend' |
| CONCAT_WS | Concatenates strings with a separator | CONCAT_WS('-', 'data', 'bend') → 'data-bend' |
| INSERT | Inserts a string at a specified position | INSERT('databend', 5, 0, 'cloud') → 'databcloudbend' |
| REPLACE | Replaces occurrences of a substring | REPLACE('databend', 'bend', 'cloud') → 'datacloud' |
| TRANSLATE | Replaces characters with their replacements | TRANSLATE('databend', 'abn', '123') → 'd1t12e3d' |
String Extraction
| Function | Description | Example |
|---|---|---|
| LEFT | Returns leftmost characters | LEFT('databend', 4) → 'data' |
| RIGHT | Returns rightmost characters | RIGHT('databend', 4) → 'bend' |
| SUBSTR / SUBSTRING | Extracts a substring | SUBSTR('databend', 5, 4) → 'bend' |
| MID | Extracts a substring (alias for SUBSTRING) | MID('databend', 5, 4) → 'bend' |
| SPLIT | Splits a string into an array | SPLIT('data,bend', ',') → ['data', 'bend'] |
| SPLIT_PART | Returns a specific part after splitting | SPLIT_PART('data,bend', ',', 2) → 'bend' |
String Padding and Formatting
| Function | Description | Example |
|---|---|---|
| LPAD | Left-pads a string to a length | LPAD('bend', 8, 'data') → 'databend' |
| RPAD | Right-pads a string to a length | RPAD('data', 8, 'bend') → 'databend' |
| REPEAT | Repeats a string n times | REPEAT('data', 2) → 'datadata' |
| SPACE | Returns a string of spaces | SPACE(4) → ' ' |
| REVERSE | Reverses a string | REVERSE('databend') → 'dnebtad' |
String Trimming
| Function | Description | Example |
|---|---|---|
| TRIM | Removes leading and trailing spaces | TRIM(' databend ') → 'databend' |
| TRIM_BOTH | Removes specified chars from both ends | TRIM_BOTH('xxdatabendxx', 'x') → 'databend' |
| TRIM_LEADING | Removes specified chars from start | TRIM_LEADING('xxdatabend', 'x') → 'databend' |
| TRIM_TRAILING | Removes specified chars from end | TRIM_TRAILING('databendxx', 'x') → 'databend' |
| LTRIM | Removes leading spaces | LTRIM(' databend') → 'databend' |
| RTRIM | Removes trailing spaces | RTRIM('databend ') → 'databend' |
String Information
| Function | Description | Example |
|---|---|---|
| LENGTH | Returns string length in characters | LENGTH('databend') → 8 |
| CHAR_LENGTH / CHARACTER_LENGTH | Returns string length in characters | CHAR_LENGTH('databend') → 8 |
| BIT_LENGTH | Returns string length in bits | BIT_LENGTH('databend') → 64 |
| OCTET_LENGTH | Returns string length in bytes | OCTET_LENGTH('databend') → 8 |
| INSTR | Returns position of first occurrence | INSTR('databend', 'bend') → 5 |
| LOCATE | Returns position of first occurrence | LOCATE('bend', 'databend') → 5 |
| POSITION | Returns position of first occurrence | POSITION('bend' IN 'databend') → 5 |
| STRCMP | Compares two strings | STRCMP('databend', 'datacloud') → -1 |
| JARO_WINKLER | Returns similarity between strings | JARO_WINKLER('databend', 'databand') → 0.9619047619047619 |
Case Conversion
| Function | Description | Example |
|---|---|---|
| LOWER / LCASE | Converts to lowercase | LOWER('DataBend') → 'databend' |
| UPPER / UCASE | Converts to uppercase | UPPER('databend') → 'DATABEND' |
Pattern Matching
| Function | Description | Example |
|---|---|---|
| LIKE | Pattern matching with wildcards | 'databend' LIKE 'data%' → true |
| NOT_LIKE | Negated LIKE | 'databend' NOT LIKE 'cloud%' → true |
| REGEXP / RLIKE | Pattern matching with regex | 'databend' REGEXP '^data' → true |
| NOT_REGEXP / NOT_RLIKE | Negated regex matching | 'databend' NOT REGEXP '^cloud' → true |
| REGEXP_LIKE | Returns boolean for regex match | REGEXP_LIKE('databend', '^data') → true |
| REGEXP_INSTR | Returns position of regex match | REGEXP_INSTR('databend', 'bend') → 5 |
| REGEXP_SUBSTR | Returns substring matching regex | REGEXP_SUBSTR('databend', 'bend') → 'bend' |
| REGEXP_REPLACE | Replaces regex matches | REGEXP_REPLACE('databend', 'bend', 'cloud') → 'datacloud' |
| GLOB | Unix-style pattern matching | 'databend' GLOB 'data*' → true |
Encoding and Decoding
| Function | Description | Example |
|---|---|---|
| ASCII | Returns ASCII value of first character | ASCII('D') → 68 |
| ORD | Returns Unicode code point of first character | ORD('D') → 68 |
| CHAR / CHR | Returns string of characters for given Unicode code points | CHAR(68,97,116,97) → 'Data' |
| BIN | Returns binary representation | BIN(5) → '101' |
| OCT | Returns octal representation | OCT(8) → '10' |
| HEX | Returns hexadecimal representation | HEX('ABC') → '414243' |
| UNHEX | Converts hex to binary | UNHEX('414243') → 'ABC' |
| TO_BASE64 | Encodes to base64 | TO_BASE64('databend') → 'ZGF0YWJlbmQ=' |
| FROM_BASE64 | Decodes from base64 | FROM_BASE64('ZGF0YWJlbmQ=') → 'databend' |
Miscellaneous
| Function | Description | Example |
|---|---|---|
| QUOTE | Escapes string for SQL | QUOTE('databend') → '"databend"' |
| SOUNDEX | Returns soundex code | SOUNDEX('databend') → 'D315' |
| SOUNDSLIKE | Compares soundex values | SOUNDSLIKE('databend', 'databand') → true |