SQL Reference

Character / 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
SQL
ASCII(char)
ASCII(char)
﻿
Example
SQL
SELECT ASCII('a');
SELECT ASCII('a');
﻿
Output: 97
Example
SQL
SELECT ASCII('bc');
SELECT ASCII('bc');
﻿
Output: 98
BIT_LENGTH
Returns the length of the character value in bits.
Syntax
SQL
BIT_LENGTH(char)
BIT_LENGTH(char)
﻿
Example
SQL
SELECT BIT_LENGTH('a');
SELECT BIT_LENGTH('a');
﻿
Output: 8
Example
SQL
SELECT BIT_LENGTH('ab');
SELECT BIT_LENGTH('ab');
﻿
Output: 16
Example
SQL
SELECT BIT_LENGTH('ab4');
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
SQL
CHR(integer)
CHR(integer)
﻿
Example
SQL
SELECT CHR(97);
SELECT CHR(97);
﻿
Output: a
CHAR_LENGTH
Alias for LENGTH﻿.  
CHARACTER_LENGTH
Alias for LENGTH﻿.
CONCAT
Concatenates two values, which must both be either binary, hash, or string data types.
For strings, as long as one argument is a character value, the other argument is implicitly cast to a character value. 
This function is equivalent to the || operator.
Syntax
SQL
CONCAT(value1, value2)
CONCAT(value1, value2)
﻿
Arguments
Data Type
Description
value1
BINARY, HASH or CHAR
A value to concatenate with value2. If one argument is a character value, the other is also cast to a string. 
value2
BINARY, HASH or CHAR
A value to concatenate with value1. If one argument is a character value, the other is also cast to a string. 
Example
SQL
SELECT CONCAT('ocient',' data warehouse');
SELECT CONCAT('ocient',' data warehouse');
﻿
Output: ocient data warehouse
Example
SQL
SELECT 'ocient'||' data warehouse';
SELECT 'ocient'||' data warehouse';
﻿
Output: ocient data warehouse
ENDSWITH
Returns true if x ends with y and false otherwise.
Syntax
SQL
ENDSWITH(char1, char2)
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
SQL
SELECT ENDSWITH('ocient data warehouse','warehouse');
SELECT ENDSWITH('ocient data warehouse','warehouse');
﻿
Output: true
Example
SQL
SELECT ENDSWITH('ocient data warehouse','db');
SELECT ENDSWITH('ocient data warehouse','db');
﻿
Output: false
Example
SQL
SELECT ENDSWITH('tamaño','o');
SELECT ENDSWITH('tamaño','o');
﻿
Output: true
INITCAP
For each word in the provided string, capitalize the first character if it is alphabetic. Words are defined as alphanumeric strings separated by non-alphanumeric characters. All other alphabetic characters are made lowercase.
Syntax
SQL
INITCAP(character_value)
INITCAP(character_value)
﻿
Example
SQL
SELECT INITCAP('ocient');
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 case.
Syntax
SQL
INSTR(char, char_substring)
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
SQL
SELECT INSTR('ocient dw dw', 'ocient');
SELECT INSTR('ocient dw dw', 'ocient');
﻿
Output: 1
SQL
SELECT INSTR('ocient dw dw', 'dw');
SELECT INSTR('ocient dw dw', 'dw');
﻿
Output: 8
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
SQL
LEFT(char, integer)
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
SQL
SELECT LEFT('ocient data warehouse', 8);
SELECT LEFT('ocient data warehouse', 8);
﻿
Output: ocient d
Example
SQL
SELECT LEFT('ocient data warehouse', -3);
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
SQL
LENGTH(character_or_binary_value)
LENGTH(character_or_binary_value)
﻿
Example
SQL
SELECT LENGTH('ocient data warehouse');
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
SQL
LOCATE(substring, string [, offset] )
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
SQL
SELECT LOCATE('ware', 'ocient data warehouse');
SELECT LOCATE('ware', 'ocient data warehouse');
﻿
Output: 13
Example
SQL
SELECT LOCATE('e', 'ocient');
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. 
SQL
SELECT LOCATE('e', 'ocient database', 5);
SELECT LOCATE('e', 'ocient database', 5);
﻿
Output: 15
LOWER
Alias for LCASE. 
Convert string to lowercase.
Syntax
SQL
LOWER(char)
LOWER(char)
﻿
Example
SQL
SELECT LOWER('Ocient');
SELECT LOWER('Ocient');
﻿
Output: ocient
Example
SQL
SELECT LOWER('OCIENT');
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
SQL
LPAD(string, length [, pad_character] )
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 in order to equal the length value. 
﻿
If not provided, pad_character uses a whitespace character to pad. 
Example
SQL
SELECT LPAD('ocient data warehouse',30,'ab');
SELECT LPAD('ocient data warehouse',30,'ab');
﻿
Output: ababababaocient data warehouse
Example
SQL
SELECT LPAD('ocient data warehouse',6);
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
SQL
LTRIM(string [, trim_character] )
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
SQL
SELECT LTRIM(' ocient');
SELECT LTRIM(' ocient');
﻿
Output: ocient
Example
SQL
SELECT LTRIM('aaaaaocient','a'); 
SELECT LTRIM('aaaaaocient','a'); 
﻿
Output: ocient
Example
In this example, all characters "abeo " are removed from the left side of the string. 
SQL
SELECT LTRIM('aaeabe ocient','abeo '); 
SELECT LTRIM('aaeabe ocient','abeo '); 
﻿
Output: cient
MD5
Returns the hexadecimal string (all lowercase) representing the md5 hash of char.
Syntax
SQL
MD5(char)
MD5(char)
﻿
Example
SQL
SELECT md5('ocient');
SELECT md5('ocient');
﻿
Output: 438f03cf6e9ddf8793e02db25f2d2f88
MID
Alias for SUBSTRING﻿.
OCTET_LENGTH
Returns the length in bytes of a character or binary value.
Syntax
SQL
OCTET_LENGTH(value)
OCTET_LENGTH(value)
﻿
Example
SQL
SELECT OCTET_LENGTH('a');
SELECT OCTET_LENGTH('a');
﻿
Output: 1
Example
SQL
SELECT OCTET_LENGTH('ocient');
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
SQL
REGEXP_COUNT( string, pattern [, position [, parameters ] ] )
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.
SQL
SELECT REGEXP_COUNT('ABABDaSGRESaB', 'AB', 1, 'i');
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
SQL
REGEXP_INSTR( source_string, pattern [, position [, occurrence] [, option [, parameters ] ] ] ] )
REGEXP_INSTR( source_string, pattern [, position [, occurrence] [, option [, 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. 
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. 
SQL
SELECT REGEXP_INSTR(
    'https://docs.ocient.com/character-binary-functions#J28jB', 
    '#\w+',
    9, 
    1, 
    1
    );
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
SQL
REGEXP_REPLACE( string, pattern [, replace_string [ , position [, parameters ] ] ] )
REGEXP_REPLACE( string, pattern [, replace_string [ , 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.
replace_string
CHAR
Optional. 
A string to replace all occurrences of the regular expression pattern in string.
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.
Example
The query replaces the matching substring '#J28jB' with the characters '#0FqD_'. The search begins at position 9.
SQL
SELECT REGEXP_REPLACE(
    'https://docs.ocient.com/character-binary-functions#J28jB', 
    '#\w+',
    '#0FqD_',
    9
    );
SELECT REGEXP_REPLACE(
    'https://docs.ocient.com/character-binary-functions#J28jB', 
    '#\w+',
    '#0FqD_',
    9
    );
﻿
Output: https://docs.ocient.com/character-binary-functions#0FqD_
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
SQL
REGEXP_SUBSTR( string, pattern [, position [, occurrence [, parameters ] ] ] )
REGEXP_SUBSTR( string, pattern [, position [, occurrence [, 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. 
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.
SQL
SELECT REGEXP_SUBSTR(
    'https://docs.ocient.com/character-binary-functions#J28jB', 
    '#\w+',
    9,
    1
    );
SELECT REGEXP_SUBSTR(
    'https://docs.ocient.com/character-binary-functions#J28jB', 
    '#\w+',
    9,
    1
    );
﻿
Output: #J28jB
﻿
REPEAT
Repeats the character value char a number of times equal to num.
Syntax
SQL
REPEAT(char, num)
REPEAT(char, num)
﻿
Example
SQL
SELECT REPEAT('a', 5);
SELECT REPEAT('a', 5);
﻿
Output: aaaaa
REPLACE
Replaces all occurrences of substr_to_remove in the character value string with substr_to_replace.
Syntax
SQL
REPLACE(string, substr_to_remove, substr_to_replace)
REPLACE(string, substr_to_remove, substr_to_replace)
﻿
Argument
Data Type
Description
string
CHAR
A string to be altered 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, string will be returned, including NULL values.
substr_to_replace
CHAR
A substring to replace all instances of substr_to_remove.
Example
SQL
SELECT REPLACE('abcabcabcabc', 'ab', '$');
SELECT REPLACE('abcabcabcabc', 'ab', '$');
﻿
Output: $c$c$c$c
REVERSE
Reverse the input string.
Syntax
SQL
REVERSE(char)
REVERSE(char)
﻿
Example
SQL
SELECT REVERSE('abcd');
SELECT REVERSE('abcd');
﻿
Output: dcba
RIGHT
Return the number of trailing characters in the string equal to the value integer. 
Syntax
SQL
RIGHT(char, integer)
RIGHT(char, integer)
﻿
Argument
Data Type
Description
char
CHAR
A string to be evaluated 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
SQL
SELECT RIGHT('ocient data warehouse', 8);
SELECT RIGHT('ocient data warehouse', 8);
﻿
Output: arehouse
Example
SQL
SELECT RIGHT('ocient data warehouse', -3);
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
SQL
RPAD(character_value_text, integral_value_length [, character_value_pad] )
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 in order to equal the length value. 
﻿
If not provided, pad_character uses a whitespace character to pad. 
Example
SQL
SELECT RPAD('ocient data warehouse',30);
SELECT RPAD('ocient data warehouse',30);
﻿
Output: ocient data warehouse         
Example
SQL
SELECT RPAD('ocient data warehouse',30,'ab');
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
SQL
RSUBSTRING(string, integer)
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
SQL
SELECT RSUBSTRING('ocient data warehouse',14);
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
SQL
RTRIM(string [, trim_character] )
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
SQL
SELECT RTRIM('ocient '); 
SELECT RTRIM('ocient '); 
﻿
Output: ocient
Example
SQL
SELECT RTRIM('ocientaaaaaa','a');
SELECT RTRIM('ocientaaaaaa','a');
﻿
Output: ocient
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
SQL
SPACE(repeat)
SPACE(repeat)
﻿
Example
SQL
SELECT CONCAT(SPACE(10),'end');
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
SQL
SPLIT_PART(string, delimiter, index)
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
SQL
SELECT SPLIT_PART('id|name|address','|',1);
SELECT SPLIT_PART('id|name|address','|',1);
﻿
Output: id
Example
SQL
SELECT SPLIT_PART('id|name|address','|',2);
SELECT SPLIT_PART('id|name|address','|',2);
﻿
Output: name
Example
SQL
SELECT SPLIT_PART('id,name,address',',',3);
SELECT SPLIT_PART('id,name,address',',',3);
﻿
Output: address
STARTSWITH
Returns true if string starts with substring and false otherwise.
Syntax
SQL
STARTSWITH(string, substring)
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
SQL
SELECT STARTSWITH('ocient','o');
SELECT STARTSWITH('ocient','o');
﻿
Output: true
Example
SQL
SELECT STARTSWITH('ocient','c');
SELECT STARTSWITH('ocient','c');
﻿
Output: false
STRPOS
Equivalent to using LOCATE﻿ as LOCATE(substring, string). Note the reversed argument order.
Syntax
SQL
STRPOS(string, substring)
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
SQL
SELECT STRPOS('ocient data warehouse','house');
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
SQL
SUBSTRING(string, start_position [, length] )
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
SQL
SELECT SUBSTRING('ocient data warehouse',8);
SELECT SUBSTRING('ocient data warehouse',8);
﻿
Output: data warehouse
Example
SQL
SELECT SUBSTRING('ocient data warehouse',8,4);
SELECT SUBSTRING('ocient data warehouse',8,4);
﻿
Output: data
TO_CHAR
Converts a numeric, date, or timestamp value into a CHAR date type. 
SQL
TO_CHAR(value, format)
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﻿ page.
Example
SQL
SELECT TO_CHAR(20200610132514/1000000,'9999-99-99');
SELECT TO_CHAR(20200610132514/1000000,'9999-99-99');
﻿
Output: 2020-06-10
Example
SQL
SELECT TO_CHAR(20200610132514%1000000,'99:99:99');
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
SQL
TRANSLATE(string, char_to_remove, char_to_replace)
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
SQL
SELECT TRANSLATE('abcdef','ace','ghi');
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
SQL
TRIM(string [, trim_char] )
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
SQL
SELECT TRIM(' trimmed string ');
SELECT TRIM(' trimmed string ');
﻿
Output: trimmed string
Example
SQL
SELECT TRIM('aaaaaaaatrimmed stringaaaaaaaaa', 'a');
SELECT TRIM('aaaaaaaatrimmed stringaaaaaaaaa', 'a');
﻿
Output: trimmed string
UCASE
Alias for UPPER﻿.  
UPPER
Alias for UCASE. 
Convert string to upper case.
Syntax
SQL
UPPER(character_value)
UPPER(character_value)
﻿
Example
SQL
SELECT UPPER('ocient');
SELECT UPPER('ocient');
﻿
Output: OCIENT
Related Links
﻿SIMILAR TO Operator﻿﻿
﻿Formatting Functions﻿﻿

Arguments	Data Type	Description
value1	BINARY, HASH or CHAR	A value to concatenate with value2. If one argument is a character value, the other is also cast to a string.
value2	BINARY, HASH or CHAR	A value to concatenate with value1. If one argument is a character value, the other is also cast to a string.

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.

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.

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.

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.

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 in order to equal the length value. If not provided, pad_character uses a whitespace character to pad.

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.

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.

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

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.
replace_string	CHAR	Optional. A string to replace all occurrences of the regular expression pattern in string. 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.

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

Argument	Data Type	Description
string	CHAR	A string to be altered 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, string will be returned, including NULL values.
substr_to_replace	CHAR	A substring to replace all instances of substr_to_remove.

Argument	Data Type	Description
char	CHAR	A string to be evaluated 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.

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 in order to equal the length value. If not provided, pad_character uses a whitespace character to pad.

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.

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.

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.

Argument	Data Type	Description
string	CHAR	A string to search.
substring	CHAR	A substring to find at the beginning of string.

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.

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.

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

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.

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.

Updated 29 Aug 2024

Did this page help you?

Array Functions and Operators

Data Types

Character / Binary Functions

ASCII

BIT_LENGTH

BTRIM

CHR

CHAR_LENGTH

CHARACTER_LENGTH

CONCAT

ENDSWITH

INITCAP

INSTR

LCASE

LEFT

LENGTH

LOCATE

LOWER

LPAD

LTRIM

MD5

MID

OCTET_LENGTH

POSITION

REGEXP_COUNT

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_SUBSTR

﻿

REPEAT

REPLACE

REVERSE

RIGHT

RPAD

RSUBSTRING

RTRIM

SPACE

SPLIT_PART

STARTSWITH

STRPOS

SUBSTR

SUBSTRING

TO_CHAR

TRANSLATE

TRIM

UCASE

UPPER

Related Links