Skip to main content

String functions and operators

These apply to MonetDB SQL character string types: char, varchar, clob and subtypes: json and url
In MonetDB all character strings are UTF-8 encoded.

Character string operators

Operator Description Example Result
|| concatenate two strings. Returns null when one of the strings is null 'Monet' || 'DB' MonetDB
|| concatenation of a string with a non-string input 'Value: ' || 42 Value: 42


 


 

Character string functions

Function Return type Description Example Result
ascii(s) int Unicode code point of the first character of the string s ascii('€') 8364
char_length(s) int number of UTF-8 characters in UTF-8 string s char_length('2€') 2
character_length((s) int number of UTF-8 characters in UTF-8 string s character_length(('2€') 2
charindex(substr, s) int start position of substr in string s or 0 when not found charindex('gh', 'highigh') 3
charindex(substr, s, i) int start position of substr in string s starting at offset i
or 0 when not found
charindex('gh', 'highigh', 4) 6
concat(s1, s2) same as input concatenate strings s1 and s2.
returns null when either s1 or s2 is null
concat('Front ', 242) Front 242
difference(s1, s2) int converts two strings s1 and s2 to their soundex codes and reports the number of matching code positions. Since soundex codes have four characters, the result ranges from zero to four, with zero being no match and four being an exact match. difference('MonetDB', 'DB3') 4
editdistance(s1, s2) int compute the 'distance' between two strings returning an integer between 0 (equal) and n (largest distance) where n is the length of largest string editdistance('MonetDB', 'DB3') 6
editdistance2(s1, s2) int compute the 'distance2' between two strings returning an integer between 0 (equal) and n (largest distance) where n is the length of largest string editdistance2('MonetDB', 'DB3') 6
greatest(s1, s2) same as input alphabetically ordered last string value of strings s1 and s2. (same as sql_max(s1, s2)) greatest('ab', 'ac') ac
"index"(s, boolean) int index values of internal string dictionary table. Note: the function name index needs to be surrounded by double quotes. select name, "index"(name, true) nm_idx from sys.schemas order by name 8 for sys, 24 for tmp, 40 for json, etc
insert(s1, int pos, int nr, s2) same as input arg 4 insert string s2 into string s1 after position pos replacing nr characters in string s1. when pos is negative it counts from the end of the string. nr must be >= 0. insert('abcdef', 2, 1, 'XY') abXYdef
lcase(s) same as input convert string s to lower case lcase('MonetDB') monetdb
least(s1, s2) same as input alphabetically ordered first string value of strings s1 and s2. (same as sql_min(s1, s2)) least('ab', 'ac') ab
left(s, int n) same as input arg 1 return first n characters in the string left('MonetDB', 3) Mon
length(s) int count number of UTF-8 characters in UTF-8 string s length('2€') 2
levenshtein(s1, s2) int compute the 'levenshtein distance' between two strings returning an integer between 0 (equal) and n (largest distance) where n is the length of largest string levenshtein('MonetDB', 'DB3') 6
levenshtein(s1, s2, int ??, int ??, int ??) int compute the 'levenshtein distance' between two strings returning an integer between 0 (equal) and n + ?? (largest distance) where n is the length of largest string levenshtein('MonetDB', 'DB3', 4, 2, 1) 8
locate(s1, s2) int locate string s1 in s2, when found return start position else 0 locate('DB', 'MonetDB') 6
locate(s1, s2, int pos) int locate string s1 in s2 starting from position pos, when found return start position else 0 locate('DB', 'DBMonetDB', 3) 8
lower(s) same as input convert string s to lower case lower('MonetDB') monetdb
lpad(s, int len) same as input arg 1 fill up the string to length len by prepending spaces. If the string is already longer than length then it is truncated (on the right). lpad('abc', 6)    abc
lpad(s1, int len, s2) same as input arg 1 fill up the string s1 to length len by prepending the characters s2. If the string s1 is already longer than length then it is truncated (on the right). lpad('abc', 6, 'DB') DBDabc
ltrim(s) varchar remove leading whitespace characters from string s ltrim(E' \t\fab \tdf ') ab df
ltrim(s1, s2) varchar remove leading s2 strings from string s1 ltrim('aabcdaa', 'a') bcdaa
sys.md5(s) clob(32) calculates the MD5 hash of string, returning the result as 32 character hex number sys.md5('abc') 900150983cd24fb0d6963f7d28e17f72
sys.ms_stuff(varchar(32) s1, int pos, int nr, varchar(32) s2) varchar(32) insert string s2 into string s1 at position pos replacing nr characters in string s1. If pos <= 0 or > length of s1 then an empty string is returned. nr may be negative. sys.ms_stuff('abcdef', 2, 1, 'XY') aXYcdef
octet_length(s) int number of bytes in UTF-8 string s octet_length('2€') 4
patindex(pattern, s) int search for pattern in string s, when found return the position of the last matched character, when not found return 0 patindex('%bc%', 'abcd') 3
qgramnormalize(s) same as input normalisation of a string s. Converts letters to uppercase and removes all characters not in a..zA..Z0..9 or space qgramnormalize('Prize €50!') PRIZE 50
repeat(s, nr) same as input arg 1 repeat string s the specified nr of times repeat('kiss,', 3) kiss,kiss,kiss,
replace(s, srch, repl) same as input arg 1 replace occurrences of string srch in string s by string repl replace('abc', 'b', 'X') aXc
sys.reverse(s) clob return reversed string sys.reverse('MonetDB') BDtenoM
right(s, int n) same as input arg 1 return last n characters in the string right('MonetDB', 4) etDB
rpad(s, int) same as input arg 1 fill up the string to length len by appending spaces. If the string is already longer than length then it is truncated (on the right). rpad('abc', 6) || 'End' abc   End
rpad(s1, int len, s2) same as input arg 1 fill up the string s1 to length len by appending the characters s2. If the string s1 is already longer than length then it is truncated (on the right). rpad('abc', 6, 'DB') abcDBD
rtrim(s) varchar remove trailing whitespace characters from string s rtrim(E'ab df \t\n') ab df
rtrim(s1, s2) varchar remove trailing s2 strings from string s1 rtrim('aabcdaa', 'a') aabcd
similarity(s1, s2) double computes the similarity between string s1 and s2 and returns a number between 0 (no similarity) and 1 (equal). similarity('MonetDB', 'DB3') 0.4
soundex(s) varchar(4) computes the Soundex code from string s using a phonetic algorithm soundex('MonetDB') M533
space(nr) clob(nr) generates a string with nr spaces select 's' || space(4) || 'e' s    e
splitpart(s, sep, n) same as input arg 1 extract substring from string s by spliting on separator string sep the field before nth occurrence. n must be greater than zero splitpart('a|a|bc|cd', '|', 3) bc
sql_max(s1, s2) same as input alphabetically ordered last string value of strings s1 and s2 sql_max('ab', 'ac') ac
sql_min(s1, s2) same as input alphabetically ordered first string value of strings s1 and s2 sql_min('ab', 'ac') ab
strings(s) varchar convert a column of type char(n) or varchar(n) or clob or clob(n) to type varchar(m) where m is the maximum of the string lengths of all values in the column select name, length(name) as name_len, strings(name) as nm_varchar8 from sys.schemas the result column nm_varchar8
will be of type varchar(8)
while name is defined
as varchar(1024)
substr(s, startpos) same as input arg 1 extract substring starting from position startpos substr('abCde', 3) Cde
substr(s, startpos, count) clob(count) extract substring starting from position startpos for count characters substr('abCde', 2, 3) bCd
substring(s, startpos) same as input arg 1 extract substring starting from position startpos substring('abcde', 3) cde
substring(s, startpos, count) clob(count) extract substring starting from position startpos for count characters substring('abcde', 2, 2) bc
trim(s) varchar remove leading and trailing whitespace characters from string s trim(E' \t\fab df \t\n') ab df
trim(s1, s2) varchar remove leading and trailing s2 strings from string s1 trim('aabcdaa', 'a') bcd
"truncate"(s, n_chars) same as input arg 1 truncate the string s to a maximum of n_chars characters. Note: the function name truncate need to be surrounded by double quotes. "truncate"('Truncate', 4) Trun
ucase(s) same as input convert string s to upper case ucase('MonetDB') MONETDB
upper(s) same as input convert string s to upper case upper('MonetDB') MONETDB