String Functions
This document contains the string functions supported by e6data.
| | - Concatenation Operator
returns the final string expression by concating two expressions
> select 'hello' || 'world';
'helloworld'
> select concat('hello ', 'world')
'hello world'
LIKE - Like Operator
The like
operator is used to match a specified pattern in a string expression. The pattern contains regular characters like %, _
select colA from table where colA like 'P_%'
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 %, _
> select colA from table where colA ilike 'P_%'
> select colA from table where colA not ilike 'P_%'
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 %, _
> select 'E6data' RLIKE 'E[0-9][a-z]+'
true
CHARACTER_LENGTH / CHAR_LENGTH / LEN / LENGTH ( <expr> )
Returns the length of a given string expression.
> select character_length('e6data');
6
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
> select replace('hard worker', 'hard', 'smart');
'smart worker'
> select replace('hard worker', 'hard');
'worker'
TRIM( <expr> )
Trims/removes leading and trailing whitespace in the input string expression.
> select trim(' Fastest database engine ');
'Fastest database engine'
LTRIM( <expr> )
Removes leading whitespace in the input string expression.
> select ltrim(' Fastest database engine--');
'Fastest database engine--'
RTRIM( <expr> )
Removes trailing whitespace in the input string expression.
> select rtrim('Fastest database engine ');
'Fastest database engine'
LOWER( <expr> )
Returns the input string expression converted to lowercase characters.
> select lower('FASTEST DataBAse ENgine- 2022');
'fastest database engine- 2022'
UPPER( <expr> )
Returns the input string expression converted to uppercase characters.
> select upper('Fastest database engine- 2022');
'FASTEST DATABASE ENGINE- 2022'
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.
> select substring('Fastest database engine', 9, 8)
'database'
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.
> select initcap('hello world');
'Hello World'
CHARINDEX( <expr> , string [, startindex] )
Returns the first occurrence of the starting position of the input expression within string
. Default value of startindex
is 1.
> select charindex('a', 'abc')
1
> select charindex('a', 'abc', 2)
0
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.
> select position('6' in 'e6data-e6data');
2
> select position('6' in 'e6data-e6data' from 3);
9
RIGHT ( <string>, <offset> )
Returns the rightmost substring of its input. Offset index starts from 1.
Supported datatypes are string, integer, decimal, date, timestamp
> SELECT right('abc', 2);
bc
LEFT ( <string>, <offset> )
Returns the leftmost substring of its input. Offset index starts from 1
Supported datatypes are string, integer, decimal, date, timestamp
> SELECT left('abc', 2);
ab
LOCATE ( <substring> , <string> [, <start_position> ] )
Returns the position of the first occurrence of a substring in a string
Index starts with 1
> SELECT LOCATE('6', 'e6data');
2
> SELECT LOCATE('6', 'e6data', 3);
0
CONTAINS_SUBSTR( <expr> , <search_value_literal> )
Returns TRUE if the value exists, otherwise returns FALSE.
Supported datatype are, string, integer, decimal, date, timestamp
> SELECT CONTAINS_SUBSTR('abcdef', 'ef');
TRUE
Limitations
Complex types are not supported.
Unicode characters are not supported.
INSTR( <expr>, subvalue [, position, occurrence] )
Returns the lowest position of subvalue in value.
> SELECT INSTR('helloooooooo','oo', 1, 3)
9
Limitations
Complex types are not supported.
SOUNDEX( <expr> )
Returns a string that contains a phonetic representation of the input string/integer.
> select soundex('winter')
w536
SPLIT( <expr>, [delimiter] )
Splits <expr>
using the delimiter
argument.
The default delimiter is the comma ,
Supported Datatype are string
> SELECT SPLIT('A-B-C-D-E-F-G-H', '-' )
[ A, B, C, D, E, F, G, H ]
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
> SELECT SPLIT_PART('abc@def@ghi','@',2)
def
Limitations
Unicode characters are not supported
ASCII( <expr> )
Return the ASCII code for the first character of a given string. Supported Datatype are string
> select ascii('a')
97
REPEAT( <input_string> , <repeat_value> )
Create a string by duplicating the input by number of repetitions specified.
Supported datatypes: string, integer, decimal
> select repeat('a', 4)
aaaa
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
> SELECT ENDSWITH('e6data','ta');
true
STARTSWITH / STARTS_WITH( <expr_1> , <expr_2> )
Returns TRUE if the first expression starts with second expression
Support Datatype is String
> SELECT STARTS_WITH('e6data','ta');
false
STRPOS ( <string>, <substring> [, <start_position> ] )
Returns the position of the first occurrence of a substring in a string
Index starts with 1
> SELECT STRPOS('e6data','6');
2
> SELECT STRPOS('e6data','6', 3);
0
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
> SELECT LPAD('123', 5, '0');
00123
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
> SELECT RPAD('123', 5, '0');
12300
REVERSE ( <expr> )
Returns the reversed order of characters of string, integer and boolean values
Support datatype: INT/DOUBLE/VARCHAR/BOOLEAN
> select reverse(53);
35
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
> select to_char(cast('2022-01-02 18:09:22' as timestamp), 'dd MMM yyyy hh:mm:ss a');
02 Jan 2022 06:09:22 PM
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