String Functions

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.

OperatorDescriptionExampleResult
||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: ' || 42Value: 42

Character string functions

FunctionReturn typeDescriptionExampleResult
ascii(s)intUnicode code point of the first character of the string sascii('€')8364
char_length(s)intnumber of UTF-8 characters in UTF-8 string schar_length('2€')2
character_length((s)intnumber of UTF-8 characters in UTF-8 string scharacter_length(('2€')2
charindex(substr, s)intstart position of substr in string s or 0 when not foundcharindex('gh', 'highigh')3
charindex(substr, s, i)intstart position of substr in string s starting at offset i or 0 when not foundcharindex('gh', 'highigh', 4)6
concat(s1, s2)same as inputconcatenate strings s1 and s2. returns null when either s1 or s2 is nullconcat('Front ', 242)Front 242
difference(s1, s2)intconverts two strings s1 and s2 to their soundex codes and reports the number of different code positions. Since soundex codes have four characters, the result ranges from zero to four, with zero being an exact match and four being no match.difference('MonetDB', 'DB3')4
editdistance(s1, s2)intcompute the 'distance' between two strings returning an integer between 0 (equal) and n (largest distance) where n is the length of largest stringeditdistance('MonetDB', 'DB3')6
editdistance2(s1, s2)intcompute the 'distance2' between two strings returning an integer between 0 (equal) and n (largest distance) where n is the length of largest stringeditdistance2('MonetDB', 'DB3')6
greatest(s1, s2)same as inputalphabetically ordered last string value of strings s1 and s2. (same as sql_max(s1, s2))greatest('ab', 'ac')ac
"index"(s, boolean)intindex 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 name8 for sys, 24 for tmp, 40 for json, etc
insert(s1, int pos, int nr, s2)same as input arg 4insert 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 inputconvert string s to lower caselcase('MonetDB')monetdb
least(s1, s2)same as inputalphabetically 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 1return first n characters in the stringleft('MonetDB', 3)Mon
length(s)intcount number of UTF-8 characters in UTF-8 string slength('2€')2
levenshtein(s1, s2)intcompute the 'levenshtein distance' between two strings returning an integer between 0 (equal) and n (largest distance) where n is the length of largest stringlevenshtein('MonetDB', 'DB3')6
levenshtein(s1, s2, int ??, int ??, int ??)intcompute the 'levenshtein distance' between two strings returning an integer between 0 (equal) and n + ?? (largest distance) where n is the length of largest stringlevenshtein('MonetDB', 'DB3', 4, 2, 1)8
locate(s1, s2)intlocate string s1 in s2, when found return start position else 0locate('DB', 'MonetDB')6
locate(s1, s2, int pos)intlocate string s1 in s2 starting from position pos, when found return start position else 0locate('DB', 'DBMonetDB', 3)8
lower(s)same as inputconvert string s to lower caselower('MonetDB')monetdb
lpad(s, int len)same as input arg 1fill 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 1fill 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)varcharremove leading whitespace characters from string sltrim(E' \t\fab \tdf ')ab df
ltrim(s1, s2)varcharremove leading s2 strings from string s1ltrim('aabcdaa', 'a')bcdaa
sys.md5(s)clob(32)calculates the MD5 hash of string, returning the result as 32 character hex numbersys.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)intnumber of bytes in UTF-8 string soctet_length('2€')4
patindex(pattern, s)intsearch for pattern in string s, when found return the position of the last matched character, when not found return 0patindex('%bc%', 'abcd')3
qgramnormalize(s)same as inputnormalisation of a string s. Converts letters to uppercase and removes all characters not in a..zA..Z0..9 or spaceqgramnormalize('Prize €50!')PRIZE 50
repeat(s, nr)same as input arg 1repeat string s the specified nr of timesrepeat('kiss,', 3)kiss,kiss,kiss,
replace(s, srch, repl)same as input arg 1replace occurrences of string srch in string s by string replreplace('abc', 'b', 'X')aXc
sys.reverse(s)clobreturn reversed stringsys.reverse('MonetDB')BDtenoM
right(s, int n)same as input arg 1return last n characters in the stringright('MonetDB', 4)etDB
rpad(s, int)same as input arg 1fill 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 1fill 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)varcharremove trailing whitespace characters from string srtrim(E'ab df \t\n')ab df
rtrim(s1, s2)varcharremove trailing s2 strings from string s1rtrim('aabcdaa', 'a')aabcd
similarity(s1, s2)doublecomputes 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 algorithmsoundex('MonetDB')M533
space(nr)clob(nr)generates a string with nr spacesselect 's' || space(4) || 'e's    e
splitpart(s, sep, n)same as input arg 1extract substring from string s by spliting on separator string sep the field before nth occurrence. n must be greater than zerosplitpart('a|a|bc|cd', '|', 3)bc
sql_max(s1, s2)same as inputalphabetically ordered last string value of strings s1 and s2sql_max('ab', 'ac')ac
sql_min(s1, s2)same as inputalphabetically ordered first string value of strings s1 and s2sql_min('ab', 'ac')ab
strings(s)varcharconvert 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 columnselect name, length(name) as name_len, strings(name) as nm_varchar8 from sys.schemasthe result column nm_varchar8 will be of type varchar(8) while name is defined as varchar(1024)
substr(s, startpos)same as input arg 1extract substring starting from position startpossubstr('abCde', 3)Cde
substr(s, startpos, count)clob(count)extract substring starting from position startpos for count characterssubstr('abCde', 2, 3)bCd
substring(s, startpos)same as input arg 1extract substring starting from position startpossubstring('abcde', 3)cde
substring(s, startpos, count)clob(count)extract substring starting from position startpos for count characterssubstring('abcde', 2, 2)bc
trim(s)varcharremove leading and trailing whitespace characters from string strim(E' \t\fab df \t\n')ab df
trim(s1, s2)varcharremove leading and trailing s2 strings from string s1trim('aabcdaa', 'a')bcd
"truncate"(s, n_chars)same as input arg 1truncate 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 inputconvert string s to upper caseucase('MonetDB')MONETDB
upper(s)same as inputconvert string s to upper caseupper('MonetDB')MONETDB