Character and Binary Functions

all string functions support unicode characters functions that transform character case are locale sensitive these functions support the utf 8 format rtrim ltrim left right trim translate rpad lpad substring index position values for character and string functions begin at position 1 for example, the "h" in the string "hello" is at position 1 ascii returns the ascii code value of the leftmost character of the character value the ascii function only supports ascii characters in the input argument syntax ascii(char) example select ascii('a'); output 97 example select ascii('bc'); output 98 bit length returns the length of the character value in bits syntax bit length(char) example select bit length('a'); output 8 example select bit length('ab'); output 16 example select bit length('ab4'); output 24 btrim alias for character and binary functions docid\ t4v9mfehdtbx8w 6ipulp chr converts an integer value to a string the value is first sign extended to 8 bytes then, if it can be represented as 1 byte, a string is returned with that one byte otherwise, a string is returned of length 2 bytes, 4 bytes, or 8 bytes with the bytes set to the big endian representation of the integer, depending on how many high order zero bytes there are in the integer syntax chr(integer) example select chr(97); output a char length alias for character and binary functions docid\ t4v9mfehdtbx8w 6ipulp character length alias for character and binary functions docid\ t4v9mfehdtbx8w 6ipulp concat concatenates two or more values, all of which must be binary, hash, or string data types for strings, as long as one argument is a character value, the other arguments are implicitly cast to a character value this function is equivalent to the || operator syntax concat(value1, value2 \[, ]) arguments data type description value1, value2 \[, ] binary , hash or char two or more values to concatenate if any argument is a character value, the others are also cast to a character value example select concat('ocient',' ','data', ' ', 'warehouse'); output ocient data warehouse example select 'ocient'||' data warehouse'; output ocient data warehouse endswith returns true if x ends with y and false otherwise syntax endswith(char1, char2) argument data type description char1 char a string to evaluate for whether it ends with char2 char2 char a string to evaluate for whether char1 ends with it as a substring example select endswith('ocient data warehouse','warehouse'); output true example select endswith('ocient data warehouse','db'); output false example select endswith('tamaño','o'); output true initcap for each word in the specified string, capitalize the first character if it is alphabetic the system defines words as alphanumeric strings separated by non alphanumeric characters the system converts all other alphabetic characters to lowercase syntaxes initcap(char string) initcap(char string, delimiter) argument data type description char string char a string to evaluate for initial capitalization delimiter char optional one or more non alphanumeric characters that specify the delimiter between words the characters include all the ascii printable non alphanumeric characters except backquotes ` and equals = non ascii characters are not included ℹ️if you specify alphabetic characters for the delimiter, the function converts them to lowercase example select initcap('ocient'); output ocient instr returns the index position of the first occurrence where the character value char substring appears in the character value char by ignoring the case syntax instr(char, char substring) argument data type description char char a string to evaluate for the first index position where char substring appears char substring char a string to evaluate for where it first appears in char example select instr('ocient dw dw', 'ocient'); output 1 select instr('ocient dw dw', 'dw'); output 8 json extract path text returns the value for the key value pair referenced by a series of path elements in a json string syntax json extract path text ( json string, path \[, path2 \[, ] ] \[, null if invalid ] ) argument data type description json string char a json string path \[, path2 \[, ] ] char one or more path elements in a json string you must include at least one path element up to a maximum of five, meaning the function can extract from paths nested up to five levels deep in the json string path elements are case sensitive if a specified path element does not exist in the json string, the function returns null null if invalid boolean optional if you set this value to true , the function returns null if the json string is invalid if you set this value to false , the function returns an error when the json string is invalid the default value is false examples extract values from nested paths this example extracts the value based on two specified paths in the json string, n4 and n6 select json extract path text( '{"n2" {"n3" 1},"n4" {"n5" 99,"n6" "circle"}}', 'n4', 'n6' ); output "circle" return null from invalid json in this example, the query requests the same nested paths, but the json is invalid because all of the keys lack quotation marks the query returns null because the null if invalid argument equals true select json extract path text( '{n2 {n3 1},n4 {n5 99,n6 "circle"}}', 'n4', 'n6', true ); output null lcase alias for character and binary functions docid\ t4v9mfehdtbx8w 6ipulp left return the number of characters in the string equal of the value integer if integer is negative, the function returns all but the last integer characters syntax left(char, integer) argument data type description char char a string to be modified by returning the number of characters equal to integer integer int the number of characters to return from the char string if integer is negative, the function returns all but the last integer characters example select left('ocient data warehouse', 8); output ocient d example select left('ocient data warehouse', 3); output ocient data wareho length alias for char length and character length returns the length of the value for character data types, this value is in terms of characters for binary data types, this value is in terms of bytes syntax length(character or binary value) example select length('ocient data warehouse'); output 21 locate alias for position returns the index position of the first occurrence of the character value substring in the character value string optionally, you can also include an additional value offset to offset the locate function by the specified number of spaces index positions begin at 1 syntax locate(substring, string \[, offset] ) argument data type description substring char a substring to be found for its first position in string string char a string to be evaluated for the first position of substring offset int optional a number of index positions in string to offset the search for the substring value this index position must be a positive integer, and it starts from the left of string example select locate('ware', 'ocient data warehouse'); output 13 example select locate('e', 'ocient'); output 4 example in this example, the index starts at position 5 this means the function skips the initial "e" in the string instead, it returns the second e at index position 15 select locate('e', 'ocient database', 5); output 15 lower alias for lcase convert string to lowercase syntax lower(char) example select lower('ocient'); output ocient example select lower('ocient'); output ocient lpad pad the input text to the specified length with the pad string on the left side if text is longer than length, it is truncated to length characters if the argument pad is not provided, the space character is used syntax lpad(string, length \[, pad character] ) argument data type description string char a string to be padded on its left side with additional characters, so that it equals the length value length int the number of characters for string to total if the string is already longer than length , the function truncates string to equal the length value pad character char optional a string of one or more characters to use to pad string to equal the length value if not provided, pad character uses a whitespace character to pad example select lpad('ocient data warehouse',30,'ab'); output ababababaocient data warehouse example select lpad('ocient data warehouse',6); output ocient ltrim removes leading blanks from the string value string alternatively, you can specify a second string value trim character if you specify the trim character value, the ltrim function removes all leading instances of the trim character value from the string syntax ltrim(string \[, trim character] ) argument data type description string char a string to be trimmed on its left side trim character char optional a string of one or more characters to be trimmed from the left side of string each character is trimmed individually, not as a cohesive substring if you do not specify this argument, this value defaults to removing all whitespace from the left side of string example select ltrim(' ocient'); output ocient example select ltrim('aaaaaocient','a'); output ocient example in this example, all characters "abeo " are removed from the left side of the string select ltrim('aaeabe ocient','abeo '); output cient md5 returns the hexadecimal string (all lowercase) representing the md5 hash of char syntax md5(char) example select md5('ocient'); output 438f03cf6e9ddf8793e02db25f2d2f88 mid alias for character and binary functions docid\ t4v9mfehdtbx8w 6ipulp octet length returns the length in bytes of a character or binary value syntax octet length(value) example select octet length('a'); output 1 example select octet length('ocient'); output 6 position alias for character and binary functions docid\ t4v9mfehdtbx8w 6ipulp regexp count searches a string for all occurrences of a regular expression pattern the function returns an integer representing the number of times the regular expression pattern occurs in the string syntax regexp count( string, pattern \[, position \[, parameters ] ] ) argument data type description string char the string to search, specified as a character string pattern char the regular expression pattern to use in the search, specified as a character string position int optional the position, specified as a positive integer, that represents the position within the string string where to begin the search, based on the number of characters if this integer exceeds the number of characters in string , then the function returns 0 the default value is 1 parameters char optional parameters, specified as a character string, that contains one or more characters representing regular expression options for pattern matching supported options are c — perform case sensitive matching the {{ocient}} system enables this type of matching by default i — perform case insensitive matching p — interpret the pattern using the perl compatible regular expression (pcre) dialect for details, see pcre the ocient system enables this interpretation by default example this example searches the 'ababdasgresab' string for the count of occurrences of the 'ab' string by ignoring the case sensitivity the search starts at position 1 select regexp count('ababdasgresab', 'ab', 1, 'i'); output 3 regexp instr searches a string using a regular expression pattern and returns an integer representing the start position or end position of the substring that matches the function returns 0 if no match is found the regexp instr function is similar to the character and binary functions docid\ t4v9mfehdtbx8w 6ipulp function, but it allows greater precision with regular expressions syntax regexp instr( source string, pattern \[, position \[, occurrence] \[, option \[, parameters ] ] ] ] ) argument data type description string char the string to search, specified as a character string note that sql escape sequences for string literals can override regular expression escape sequences for details, see general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l pattern char the regular expression pattern to use in the search, specified as a character string position int optional a positive integer that represents the position within the string string where to begin the search, based on the number of characters this value alters only the start position for the match, not the returned string position if this integer exceeds the number of characters in string , then the function returns 0 the default value is 1 occurrence int optional a positive integer that represents the occurrence of a positive pattern match to return the default value is 1 , which means the function returns the first substring that matches the regular expression pattern option int optional an integer that specifies whether to return the position of the matching start position or the end position your choices are 0 returns the start position of the match 1 returns the end position of the match +1 the function treats any nonzero integer value as 1 the default value is 0 parameters char optional a string containing one or more characters that represents the regular expression options for pattern matching supported options are c — perform case sensitive matching the ocient system enables this type of matching by default i — perform case insensitive matching e — extract the substring using a regular expression subpattern this subpattern is enclosed in parentheses in the regular expression the function uses the full regular expression pattern for the match but returns only the first subpattern match p — interpret the pattern using the perl compatible regular expression (pcre) dialect for details, see pcre the ocient system enables this interpretation by default example the query searches the website url for the substring preceded by the # character the function includes optional arguments to start the search at position 9 and match the first occurrence the last specified optional argument directs the function to return the ending position of the matching substring select regexp instr( 'https //docs ocient com/character binary functions#j28jb', '#\w+', 9, 1, 1 ); output 57 regexp replace searches a string for all occurrences of a regular expression pattern the function replaces every match occurrence of the pattern with a new string the regexp replace function is similar to the character and binary functions docid\ t4v9mfehdtbx8w 6ipulp and character and binary functions docid\ t4v9mfehdtbx8w 6ipulp functions, but it allows greater precision with regular expressions syntax regexp replace( string, pattern \[, replace string \[ , position \[, parameters ] ] ] ) argument data type description string char the string to search, specified as a character string note that sql escape sequences for string literals can override regular expression escape sequences for details, see general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l pattern char the regular expression pattern to use in the search, specified as a character string replace string char optional a string to replace all occurrences of the regular expression pattern in string this string can include references to capture groups in the regular expression pattern to reference capture groups, use a $ followed by the group number for example, $1 references the first capture group, and $2 references the second the default value is an empty string '' position int optional a positive integer that represents the position within the string to begin the search, based on the number of characters if this integer exceeds the number of characters in string , then the function returns the original string the default value is 1 parameters char optional a string containing one or more characters representing regular expression options for pattern matching supported options are c — perform case sensitive matching the ocient system enables this type of matching by default i — perform case insensitive matching p — interpret the pattern using the perl compatible regular expression (pcre) dialect for details, see pcre the ocient system enables this interpretation by default examples replace text using a substring the query replaces the matching substring '#j28jb' with the characters '#0fqd ' the search begins at position 9 select regexp replace( 'https //docs ocient com/character binary functions#j28jb', '#\w+', '#0fqd ', 9 ); output https //docs ocient com/character binary functions#0fqd replace text using multiple capture groups this example uses three capture groups to take an unformatted phone number and convert it into the (xxx) xxx xxxx format the replace string argument references each capture group as $1 , $2 , and $3 select regexp replace( '5558675309', '(\d{3})(\d{3})(\d{4})', '($1) $2 $3' ); output (555) 867 5309 regexp substr returns one substring from a string that matches a specified regular expression pattern regexp substr is similar to the character and binary functions docid\ t4v9mfehdtbx8w 6ipulp function, but it allows greater precision with regular expressions if the pattern produces no matches, the function returns an empty string syntax regexp substr( string, pattern \[, position \[, occurrence \[, parameters ] ] ] ) argument data type description string char the string to search, specified as a character string note that sql escape sequences for string literals can override regular expression escape sequences for details, see general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l pattern char the regular expression pattern to use in the search, specified as a character string position int optional a positive integer that represents the position within the string to begin the search, based on the number of characters if this integer is greater than the number of characters in string , then the function returns the original string the default value is 1 occurrence int optional a positive integer that represents the occurrence of a positive pattern match to return if this value exceeds the number of matches, then the function returns null the default value is 1 , which means the function returns the first substring that matches the regular expression pattern parameters char optional a string containing one or more characters representing regular expression options for pattern matching supported options are c — perform case sensitive matching the ocient system enables this type of matching by default i — perform case insensitive matching e — extract the substring using a regular expression subpattern this subpattern is enclosed in parentheses in the regular expression the function uses the full regular expression pattern for the match, but it returns only the first subpattern match if there is no subexpression in the pattern argument, then the e parameter is ignored p — interpret the pattern using the perl compatible regular expression (pcre) dialect for details, see pcre the ocient system enables this interpretation by default example the query returns the first occurrence of the #j28jb string by using the regular expression pattern '#\w+' the search starts at position 9 select regexp substr( 'https //docs ocient com/character binary functions#j28jb', '#\w+', 9, 1 ); output #j28jb repeat repeats the char string num times without spaces syntax repeat(char, num) example repeat the a string five times select repeat('a', 5); output aaaaa replace replaces all occurrences of substr to remove in the character value string with substr to replace syntax replace(string, substr to remove, substr to replace) argument data type description string char a string to alter by replacing all instances of substr to remove with substr to replace substr to remove char a substring to remove from string , replacing all instances with the substr to replace value if substr to remove is the empty string, the system returns string substr to replace char a substring to replace all instances of substr to remove example select replace('abcabcabcabc', 'ab', '$'); output $c$c$c$c reverse reverse the input string syntax reverse(char) example select reverse('abcd'); output dcba right return the number of trailing characters in the string equal to the value integer syntax right(char, integer) argument data type description char char a string to evaluate to return the number of trailing characters equal to integer integer int the number of characters of return from the end of the char string if integer is negative, the function returns all but the first integer characters example select right('ocient data warehouse', 8); output arehouse example select right('ocient data warehouse', 3); output ent data warehouse rpad pad the input text to the specified length with the pad string on the right side if text is longer than length, it is truncated to length characters if the argument pad is not provided, the space character is used syntax rpad(character value text, integral value length \[, character value pad] ) argument data type description string char a string to be padded on its right side with additional characters, so that it equals the length value length int the number of characters for string to total if the string is already longer than length , the function truncates string to equal the length value pad character char optional a string of one or more characters to use to pad string to equal the length value if not provided, pad character uses a whitespace character to pad example select rpad('ocient data warehouse',30); output ocient data warehouse example select rpad('ocient data warehouse',30,'ab'); output ocient data warehouseababababa rsubstring returns the substring from the right side of a string, based on a specified length syntax rsubstring(string, integer) argument data type description string char a string to be evaluated to return a substring based on the integer value integer int the number of characters to return from the right side of the string value this value must be positive example select rsubstring('ocient data warehouse',14); output data warehouse rtrim removes trailing blanks from the string value string alternatively, you can specify a second string value trim character if you specify the trim character value, the rtrim function removes all trailing instances of the trim character value from the string syntax rtrim(string \[, trim character] ) argument data type description string char a string to be trimmed on its right side trim character char optional a string of one or more characters to be trimmed from the right side of string each character is trimmed individually, not as a cohesive substring if you do not specify this argument, this value defaults to removing all whitespace from the right side of string example select rtrim('ocient '); output ocient example select rtrim('ocientaaaaaa','a'); output ocient sha1 uses the sha 1 cryptographic hash function to convert a string into a 40 character string representing the hexadecimal value of a 160 bit checksum syntax sha1(string) argument data type description string char a string to convert using the sha 1 cryptographic hash function example select sha1('ocient'); output 0c2a9a042b9f047f875c3414e7a4f4c53efbe082 space returns a string of repeated spaces equal to the number value, repeat you can join this to another string by using the character and binary functions docid\ t4v9mfehdtbx8w 6ipulp function syntax space(repeat) example select concat(space(10),'end'); output end split part split the value string based on the delimiter value the function returns a substring from the split operation based on the index value (starting from 1) syntax split part(string, delimiter, index) argument data type description string char a string to be split based on the delimiter value delimiter char the character to use as a delimiter in string index int the index of the substring to return the first index position starts at 1 example select split part('id|name|address','|',1); output id example select split part('id|name|address','|',2); output name example select split part('id,name,address',',',3); output address split to array splits a string into an array of substrings syntax split to array(string, \[ delimiter ]) argument data type description string char a string to split into an array of substrings delimiter char optional the delimiter that divides the string the default value is a comma ( , ) example this example specifies | as the delimiter to split the input string select split to array('ab|cd|ef', '|'); output \['ab','cd','ef'] startswith returns true if string starts with substring and false otherwise syntax startswith(string, substring) argument data type description string char a string to search substring char a substring to find at the beginning of string example select startswith('ocient','o'); output true example select startswith('ocient','c'); output false strpos equivalent to using character and binary functions docid\ t4v9mfehdtbx8w 6ipulp as locate(substring, string) note the reversed argument order syntax strpos(string, substring) argument data type description string char a string to be evaluated for the first position of substring substring char a substring to be found for its first position in string example select strpos('ocient data warehouse','house'); output 17 substr alias for character and binary functions docid\ t4v9mfehdtbx8w 6ipulp substring alias for substr and mid returns the substring of a character or binary value that starts with the position specified by the second argument and that ends with the position specified by one less than the sum of the second and third arguments when the sum of the second and third arguments is less than two, the function returns the empty string syntax substring(string, start position \[, length] ) argument data type description string char a string to be truncated based on the start position and end position values start position int the starting position to return a substring the first index position starts at 1 length int optional the number of characters from the start position to include in the returned substring if unspecified, substring returns all characters after the start position example select substring('ocient data warehouse',8); output data warehouse example select substring('ocient data warehouse',8,4); output data to char converts a numeric, date, or timestamp value into a char date type to char(value, format) argument data type description value date , timestamp , or all numeric types a numeric, date, or timestamp value to be converted into a char date type format char the format used for the char conversion for information on data type formats, see the formatting functions docid\ g ia4znpotfx2kegcvv2r page example select to char(20200610132514/1000000,'9999 99 99'); output 2020 06 10 example select to char(20200610132514%1000000,'99 99 99'); output 13 25 14 translate replaces specified characters in a provided string with a separate set of characters characters specified in the char to remove set are replaced with characters in the char to replace set based on the corresponding index position syntax translate(string, char to remove, char to replace) argument data type description string char a string to have specific characters transposed based on the char to remove and char to replace values char to remove char the characters to be removed from string characters specified in the char to remove set are replaced with characters in the char to replace set based on the corresponding index position if char to remove is longer than char to replace , occurrences of the extra characters are removed char to replace char the characters to replace the removed characters in char to remove example select translate('abcdef','ace','ghi'); output gbhdif trim alias for btrim trim leading and trailing blanks from the string alternatively, you can specify a second string value trim char if a trim char value is provided, the trim function removes all leading and trailing instances of the trim char value from the string syntax trim(string \[, trim char] ) argument data type description string char a string to be trimmed of leading and trailing whitespace, or any other characters specified by the trim char argument trim char char optional if specified, this removes an alternative substring from the start or end of the string value example select trim(' trimmed string '); output trimmed string example select trim('aaaaaaaatrimmed stringaaaaaaaaa', 'a'); output trimmed string ucase alias for character and binary functions docid\ t4v9mfehdtbx8w 6ipulp upper alias for ucase convert string to upper case syntax upper(character value) example select upper('ocient'); output ocient related links general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l formatting functions docid\ g ia4znpotfx2kegcvv2r