Character and Binary Functions

ASCII

Returns the ASCII code value of the leftmost character of the character value.

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 TRIM.

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 LENGTH.

CHARACTER_LENGTH

Alias for LENGTH.

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 [, ...])

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)

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)

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)

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 ] )

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 LOWER.

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)

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] )

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] )

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] )

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 SUBSTRING.

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 LOCATE.

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 ] ] )

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 POSITION function, but it allows greater precision with regular expressions.

Syntax

REGEXP_INSTR( source_string, pattern [, position [, occurrence] [, option [, parameters ] ] ] ] )

• 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 REPLACE and TRANSLATE functions, but it allows greater precision with regular expressions.

Syntax

REGEXP_REPLACE( string, pattern [, replace_string [ , position [, parameters ] ] ] )

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 SUBSTR 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 ] ] ] )

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)

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)

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] )

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)

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] )

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)

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 CONCAT 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)

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 ])

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)

Example

SELECT STARTSWITH('ocient','o');

Output:true

Example

SELECT STARTSWITH('ocient','c');

Output:false

STRPOS

Equivalent to using LOCATE as LOCATE(substring, string). Note the reversed argument order.

Syntax

STRPOS(string, substring)

Example

SELECT STRPOS('ocient data warehouse','house');

Output:17

SUBSTR

Alias for SUBSTRING.

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] )

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)

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)

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] )

Example

SELECT TRIM(' trimmed string ');

Output:trimmed string

Example

SELECT TRIM('aaaaaaaatrimmed stringaaaaaaaaa', 'a');

Output:trimmed string

UCASE

Alias for UPPER.

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

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

WHERE

Formatting Functions