String Functions
This document contains the string functions supported by e6data.
| | - Concatenation Operator
returns the final string expression by concating two expressions
LIKE - Like Operator
The like
operator is used to match a specified pattern in a string expression. The pattern contains regular characters like %, _
ILIKE - ILike Operator
The ilike
is a case-insensitive operator used to match a specified pattern in a string expression. The pattern contains regular characters like %, _
RLIKE - RLike Operator
The ilike
is a case-insensitive operator used to match a specified pattern in a string expression. The pattern contains regular characters like %, _
CHARACTER_LENGTH / CHAR_LENGTH / LEN / LENGTH ( <expr> )
Returns the length of a given string expression.
REPLACE( <expr>, <search> [, <replace> ] )
expr
- string expression to be searched/modified.
search
- string expression to be searched for in the input expression.
replace
- optional string expression to be replaced with the search expression. Default is an empty string.
Replaces all instances of search
with replace
in string expression
TRIM( <expr> )
Trims/removes leading and trailing whitespace in the input string expression.
LTRIM( <expr> )
Removes leading whitespace in the input string expression.
RTRIM( <expr> )
Removes trailing whitespace in the input string expression.
LOWER( <expr> )
Returns the input string expression converted to lowercase characters.
UPPER( <expr> )
Returns the input string expression converted to uppercase characters.
SUBSTRING( <expr>, start, length )
start
- numeric expression representing a starting position in the string.
length
- numeric expression representing the length of the substring.
Returns a substring from an input string
expression of the given length
beginning from the start
index.
Note: Indexing starts from 1.
SUBSTR( <expr>, start, length )
This is an alias of the substring function
INITCAP( <expr> )
Returns string expression with the first letter of each word converted to uppercase.
CHARINDEX( <expr> , string [, startindex] )
Returns the first occurrence of the starting position of the input expression within string
. Default value of startindex
is 1.
POSITION( <expr> in <expr> from [startindex] )
Returns first occurrence of the starting position of the input expression within string
. Default value of startindex
is 1.
RIGHT ( <string>, <offset> )
Returns the rightmost substring of its input. Offset index starts from 1.
Supported datatypes are string, integer, decimal, date, timestamp
LEFT ( <string>, <offset> )
Returns the leftmost substring of its input. Offset index starts from 1
Supported datatypes are string, integer, decimal, date, timestamp
LOCATE ( <substring> , <string> [, <start_position> ] )
Returns the position of the first occurrence of a substring in a string
Index starts with 1
CONTAINS_SUBSTR( <expr> , <search_value_literal> )
Returns TRUE if the value exists, otherwise returns FALSE.
Supported datatype are, string, integer, decimal, date, timestamp
Limitations
Complex types are not supported.
Unicode characters are not supported.
INSTR( <expr>, subvalue [, position, occurrence] )
Returns the lowest position of subvalue in value.
Limitations
Complex types are not supported.
SOUNDEX( <expr> )
Returns a string that contains a phonetic representation of the input string/integer.
SPLIT( <expr>, [delimiter] )
Splits <expr>
using the delimiter
argument.
The default delimiter is the comma ,
Supported Datatype are string
Limitations
Even after converting to Varchar, the passing of a date or timestamp is not supported.
SPLIT_PART( <expr>, <delimiter>, <position> )
Splits a given <expr>
based on the <delimiter>
Supported Datatype are string
Limitations
Unicode characters are not supported
ASCII( <expr> )
Return the ASCII code for the first character of a given string. Supported Datatype are string
REPEAT( <input_string> , <repeat_value> )
Create a string by duplicating the input by number of repetitions specified.
Supported datatypes: string, integer, decimal
Usages:
Output is NULL, if either <input_string> or <repeat_value> is NULL
Unicodes are not supported in <input_string>
ENDSWITH / ENDS_WITH( <expr_1> , <expr_2> )
Returns TRUE if the first expression ends with second expression
Support Datatype is String
STARTSWITH / STARTS_WITH( <expr_1> , <expr_2> )
Returns TRUE if the first expression starts with second expression
Support Datatype is String
STRPOS ( <string>, <substring> [, <start_position> ] )
Returns the position of the first occurrence of a substring in a string
Index starts with 1
LPAD ( <expr>, <length> [, <pad> ] )
LPAD
function is used to left-pad a string with a specified character or set of characters to a certain length
RPAD ( <expr>, <length> [, <pad> ] )
RPAD
function is used to right-pad a string with a specified character or set of characters to a certain length
REVERSE ( <expr> )
Returns the reversed order of characters of string, integer and boolean values
Support datatype: INT/DOUBLE/VARCHAR/BOOLEAN
TO_CHAR/TO_VARCHAR ( <expr> )
TO_CHAR/TO_VARCHAR ( <date_or_timestamp_expr> [, '<format>' ] )
This function transforms the given expression into a string.
Support datatype: INT/FLOAT/DOUBLE/STRING/DATE/TIMESTAMP/BOOL
Usages:
we support java format string for date and timestamp type. However we are not supporting zone as the format
In the formate string we don’t support
#
as it is a reserved keyword.We don’t support format parameter for other data type except for date and timestamp type
Last updated