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'
REGEXP_SPLIT_TO_ARRAY	Split a string into an array of segments using the specified pattern	regexp_split_to_array('apple,banana,orange', ','); → '['apple','banana','orange']'
REGEXP_SPLIT_TO_TABLE	Split a string into a table of segments using the specified pattern	regexp_split_to_table('data,bend', ',', 2)

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