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 docid\ ja8cont33tonx ktruedj 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 docid\ ja8cont33tonx ktruedj character length alias for docid\ ja8cont33tonx ktruedj 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 syntax concat(value1, value2 \[, ]) 153,163,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 116,138,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 161,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 161,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 ] ) 170,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ ja8cont33tonx ktruedj 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) 101,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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] ) 101,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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] ) 161,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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] ) 158,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ ja8cont33tonx ktruedj 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 docid\ ja8cont33tonx ktruedj 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 ] ] ) 158,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ ja8cont33tonx ktruedj function, but it allows greater precision with regular expressions syntax regexp instr( source string, pattern \[, position \[, occurrence] \[, option \[, parameters ] ] ] ] ) 158,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ ja8cont33tonx ktruedj and docid\ ja8cont33tonx ktruedj functions, but it allows greater precision with regular expressions syntax regexp replace( string, pattern \[, replace string \[ , position \[, parameters ] ] ] ) 158,105,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ ja8cont33tonx ktruedj 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 ] ] ] ) 158,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 74,84,502 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 101,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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] ) 148,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 154,117,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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] ) 158,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select rtrim('ocient '); output ocient example select rtrim('ocientaaaaaa','a'); output ocient sha1 uses the https //en wikipedia org/wiki/sha 1# text=in%20cryptography%2c%20sha%2d1%20(,rendered%20as%2040%20hexadecimal%20digits cryptographic hash function to convert a string into a 40 character string representing the hexadecimal value of a 160 bit checksum syntax sha1(string) 170,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ ja8cont33tonx ktruedj 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) 101,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 ]) 146,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 146,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select startswith('ocient','o'); output true example select startswith('ocient','c'); output false strpos equivalent to using docid\ ja8cont33tonx ktruedj as locate(substring, string) note the reversed argument order syntax strpos(string, substring) 144,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select strpos('ocient data warehouse','house'); output 17 substr alias for docid\ ja8cont33tonx ktruedj 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] ) 158,103,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 132,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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) 175,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select translate('abcdef','ace','ghi'); output gbhdif trim alias for btrim trim leading and trailing space characters 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] ) 128,127,375 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type example select trim(' trimmed string '); output trimmed string example select trim('aaaaaaaatrimmed stringaaaaaaaaa', 'a'); output trimmed string ucase alias for docid\ ja8cont33tonx ktruedj upper alias for ucase convert string to upper case syntax upper(character value) example select upper('ocient'); output ocient concatenate operator( || ) the || operator concatenates two strings if you specify a null string, the result is null || syntax string1 || string2 true 220,220,222left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type examples concatenate two strings concatenate two strings select 'hello ' || 'world'; output 'hello world' concatenate a null string concatenate two strings, one of which is null select null || 'world'; output null related links docid\ nw9vavkey2v75moxm muo docid\ naengoslrgk2ikpyh6lkc