Skip to content

String & Binary Operations#

ASCII#

Syntax

ASCII(<expr>)

Description

Returns the ASCII code for the first character of a string. The value 0 is returned for either of the following cases: The first character of the string contains the ASCII character corresponding to 0. The string is empty.

View examples

Formula Editor Example

SQL Editor Example

select column1, ascii(column1)
  from (values('!'), ('A'), ('a'), ('bcd'), (''), (null));
COLUMN1 ASCII(COLUMN1)
! 33
A 65
a 97
bcd 98
0
NULL NULL

BIT_LENGTH#

Syntax

IT_LENGTH(<string_or_binary>)

Description

Returns the length of a string or binary value in bits.

View examples

Formula Editor Example

SQL Editor Example

create table bl (v varchar, b binary);
  insert into bl (v, b) values
    ('abc', null),
    ('\u0394', x'A1B2');
select v, b, bit_length(v), bit_length(b) from bl order by v;
V B BIT_LENGTH(V) BIT_LENGTH(B)
abc NULL 24 NULL
Δ A1B2 16 16

CONCAT#

Syntax

CONCAT(<expr1> [, <exprN> ...])

Description

Concatenates one or more strings, or concatenates one or more binary values.

View examples

Formula Editor Example

SQL Editor Example

Concatenate two strings:

select concat('George Washington ', 'Carver');
CONCAT('GEORGE WASHINGTON ', 'CARVER')
George Washington Carver

ENDSWITH#

Syntax

ENDSWITH( <expr1> , <expr2> )

Description

Returns TRUE if the first expression ends with second expression. Returns a BOOLEAN. The value is True if expression 1 ends with expression 2. Returns NULL if either input expression is NULL. Otherwise, returns False.

View examples

Formula Editor Example

SQL Editor Example

select * from strings;
S
coffee
ice tea
latte
tea
[NULL]
select * from strings where endswith(s, 'te');
S
latte

INITCAP#

Syntax

INITCAP( <expr1> )

Description

Returns the input string with the first letter of each word in uppercase and the subsequent letters in lowercase.

View examples

Formula Editor Example

SQL Editor Example

select v, initcap(v) from testinit;
C1 INITCAP(C1)
The Quick Gray Fox The Quick Gray Fox
the sky is blue The Sky Is Blue
OVER the River 2 Times Over The River 2 Times
WE CAN HANDLE THIS We Can Handle This
HelL0_hi+therE Hell0_Hi+There
νησί του ποταμού Νησί Του Ποταμού
ÄäÖößÜü Ääöößüü
Hi,are?you!there Hi,Are?You!There
to je dobré To Je Dobré
ÉéÀàè]çÂâ ÊêÎÔô ÛûËÏ ïÜŸÇç ŒœÆæ Ééààè]Çââ Êêîôô Ûûëï Ïüÿçç Œœææ
ĄąĆ ćĘęŁ łŃńÓ óŚśŹźŻż Ąąć Ćęęł Łńńó Óśśźźżż
АаБб ВвГгД дЕеЁёЖ жЗзИиЙй Аабб Ввггд Дееёёж Жззиийй
ХхЦц ЧчШш ЩщЪъ ЫыЬь ЭэЮ юЯя Ххцц Ччшш Щщъъ Ыыьь Ээю Юяя
NULL NULL

LEFT#

Syntax

LEFT( <string_expr> , <length_expr> )

Description

Returns a leftmost substring of its input. The data type of the returned value is the same as the data type of the input value.

View examples

Formula Editor Example

SQL Editor Example

select left('ABCDEFG', 3);
LEFT('ABCDEF', 3)
ABC

LENGTH#

Syntax

LENGTH(<string_or_binary>)

Description

Returns the length of an input string or binary value. For strings, the length is the number of characters, and UTF-8 characters are counted as a single character. For binary, the length is the number of bytes.

View examples

Formula Editor Example

SQL Editor Example

select s, length (s) from strings;
s length(s)
0
Joyeux Noël 11
Merry Christmas 15
Veselé Vianoce 14
[NULL] [NULL]

LOWER#

Syntax

LOWER(<expr>)

Description

Returns the input string with all characters converted to lowercase.

View examples

Formula Editor Example

SQL Editor Example

select v, lower(v) from lu;
v lower(v)
The Quick Gray Fox the quick gray fox
LAUGHING ALL THE WAY laughing all the way
OVER the River 2 Times over the river 2 times
UuVvWwXxYyZz uuvvwwxxyyzz
ÁáÄäÉéÍíÓóÔôÚúÝý ááääééííóóôôúúýý
[NULL] [NULL]

LPAD#

Syntax

LPAD(<base>, <length_expr> [, <pad>])

Description

The argument (base) is left-padded to length length_expr with characters/bytes from the pad argument. The data type of the returned value is the same as the data type of the base input value .

View examples

Formula Editor Example

SQL Editor Example

Create and fill a table:

create table demo (v varchar, b binary);
  insert into demo (v, b) select 'Hi', hex_encode('Hi');
  insert into demo (v, b) select '-123.00', hex_encode('-123.00');
  insert into demo (v, b) select 'Twelve Dollars',
    to_binary(hex_encode('Twelve Dollars'), 'HEX');
select v, lpad(v, 10, ' '),             
      lpad(v, 10, '$')
    from demo;

| V | LPAD(V, 10, ' ') | LPAD(V, 10, '$') | |----------------+------------------+------------------| | Hi | Hi | $$$$$$$$Hi | | -123.00 | -123.00 | $$$-123.00 | | Twelve Dollars | Twelve Dol | Twelve Dol |

LTRIM#

Syntax

LTRIM( <expr> [, <characters> ] )

Description

Removes leading characters, including whitespace, from a string. The characters in characters can be specified in any order. To remove whitespace, the characters must be explicitly included in the argument. For example, ' $.' removes all leading blank spaces, dollar signs, and periods from the input string. Note that this does not remove other whitespace characters (tabulation characters, end-of-line characters, etc.), which also must be explicitly specified.

View examples

Formula Editor Example

SQL Editor Example

Remove leading 0 and # characters from a string

select ltrim('#000000123', '0#');
LTRIM('#000000123', '0#')
123

OCTET_LENGTH#

Syntax

OCTET_LENGTH(<string_or_binary>)

Description

Returns the length of a string or binary value in bytes. This will be the same as LENGTH for ASCII strings and greater than LENGTH for strings using Unicode code points. For binary, this is always the same as LENGTH.

View examples

Formula Editor Example

SQL Editor Example

select octet_length('abc'), octet_length('\u0392'), octet_length(x'A1B2');
OCTET_LENGTH('ABC') OCTET_LENGTH('\U0392') OCTET_LENGTH(X'A1B2')
3 2 2

POSITION#

Syntax

POSITION(<expr1> IN <expr2>)

Description

Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position of the first argument in the second argument. If any arguments are NULL, the function returns NULL. If the string or binary value is not found, the function returns 0. If the first argument is empty (e.g. an empty string), the function returns 1.

View examples

Formula Editor Example

SQL Editor Example

Find the first occurrence of ‘an’ in ‘banana’:

select position('an', 'banana', 1);
POSITION('AN', 'BANANA', 1)
2

REPEAT#

Syntax

REPEAT(<input>, <n>)

Description

Builds a string by repeating the input for the specified number of times.

View examples

Formula Editor Example

SQL Editor Example

select repeat('xy', 5);
REPEAT('XY', 5)
xyxyxyxyxy

REPLACE#

Syntax

REPLACE( <subject> , <pattern> [ , <replacement> ] )

Description

Removes all occurrences of a specified substring, and optionally replaces them with another string. The returned value is the string after all replacements have been done. If any of the arguments is a NULL, the result is also a NULL.

View examples

Formula Editor Example

SQL Editor Example

Replace 'bc' string in 'abcd' with an empty string:

select replace('abcd', 'bc') from dual;
REPLACE('ABCD', 'BC')
ad

Replace strings in a value with a specified replacement:

create or replace table replace_example(subject varchar(10), pattern varchar(10), replacement varchar(10));
  insert into replace_example values('snowman', 'snow', 'fire'), ('sad face', 'sad', 'happy');
select subject, pattern, replacement, replace(subject, pattern, replacement) as new from replace_example;

| SUBJECT | PATTERN | REPLACEMENT | NEW | |----------+---------+-------------+------------| | snowman | snow | fire | fireman | | sad face | sad | happy | happy face |

REVERSE#

Syntax

REVERSE(<subject>)

Description

Reverses the order of characters in a string, or of bytes in a binary value. The returned value is the same length as the input, but with the characters/bytes in reverse order. If subject is NULL, the result is also NULL.

View examples

Formula Editor Example

SQL Editor Example

select reverse('Hello, world!');
REVERSE('HELLO, WORLD!')
!dlrow ,olleH

Syntax

RIGHT( <expr> , <length_expr> )

Description

Returns a rightmost substring of its input. The data type of the returned value is the same as the data type of the input value.

View examples

Formula Editor Example

SQL Editor Example

select right('ABCDEFG', 3);
RIGHT('ABCDEFG', 3)
EFG

RPAD#

Syntax

RPAD(<base>, <length_expr> [, <pad>])

Description

Right-pads a string with characters from another string, or right-pads a binary value with bytes from another binary value. The data type of the returned value is the same as the data type of the base input value.

View examples

Formula Editor Example

SQL Editor Example

select rpad('123.50', 20, '*-') from dual;
RPAD('123.50', 20, '*-')
123.50------*-

RTRIM#

Syntax

RTRIM(<expr> [, <characters> ])

Description

Removes trailing characters, including whitespace, from a string.

View examples

Formula Editor Example

SQL Editor Example

Remove trailing '0' and '.' characters from a string:

select rtrim('$125.00', '0.');
RTRIM('$125.00', '0.')
$125

SOUNDEX#

Syntax

SOUNDEX( <varchar_expr> )

Description

Returns a string that contains a phonetic representation of the input string. This function is typically used to help determine whether two strings, such as the family names 'Levine' and 'Lavine', or the words 'to' and 'too', have similar English-language pronunciation. The returned value is a VARCHAR that contains the phonetic representation of the input string. In other words, the return value is a string (not a sound) that represents the pronunciation, rather than the spelling, of the input string. The returned value starts with a letter that represents the first letter in the string followed by 3 digits.

View examples

Formula Editor Example

SQL Editor Example

select soundex('Marks'), soundex('Marx');

| SOUNDEX('MARKS') | SOUNDEX('MARX') | |------------------+-----------------| | M620 | M620 |

SPLIT#

Syntax

SPLIT(<string>, <separator>)

Description

Splits a given string with a given separator and returns the result in an array of strings. Contiguous split strings in the source string, or the presence of a split string at the beginning or end of the source string, results in an empty string in the output. An empty separator string results in an array containing only the source string. If either parameter is a NULL, a NULL is returned.

View examples

Formula Editor Example

SQL Editor Example

Split the localhost IP address 127.0.0.1 into an array consisting of each of the four parts:

select split('127.0.0.1', '.');
SPLIT('127.0.0.1', '.')
[
"127",
"0",
"0",
"1"
]

STRTOK#

Syntax

STRTOK(<string> [,<delimiter>] [,<partNr>])

Description

Tokenizes a given string and returns the requested part. If the requested part does not exist, then NULL is returned. If any parameter is NULL, then NULL is returned. If the string starts or is terminated with the delimiter, the system considers empty space before or after the delimiter, respectively, as a valid token.

View examples

Formula Editor Example

SQL Editor Example

select strtok('a.b.c', '.', 1);
STRTOK('A.B.C', '.', 1)
a

SUBSTRING#

Syntax

SUBSTRING(<base_expr>, <start_expr> [, <length_expr>])

Description

Returns the portion of the string or binary value from , starting from the character/byte specified by , with optionally limited . The data type of the returned value is the same as the data type of the base expression. If any of the inputs are NULL, NULL is returned.

View examples

Formula Editor Example

SQL Editor Example

select '123456', pos, len, substr('123456', pos, len) from o;
'123456' pos len substr('123456', pos, len)
123456 -1 3 6
123456 -3 3 456
123456 -3 7 456
123456 -5 3 234
123456 -7 3
123456 0 3 123
123456 0 7 123456
123456 1 3 123
123456 3 3 345
123456 3 7 3456
123456 5 3 56
123456 5 7 56
123456 7 3
123456 [NULL] 3 [NULL]
123456 [NULL] 7 [NULL]

TRANSLATE#

Syntax

TRANSLATE(<subject>, <sourceAlphabet>, <targetAlphabet>)

Description

Translates from the characters in to the characters in .

View examples

Formula Editor Example

SQL Editor Example

Translate ‘X’ to ‘c’, ‘Y’ to ‘e’, ‘Z’ to ‘f’ and remove ‘❄’ characters:

select translate('❄a❄bX❄dYZ❄','XYZ❄','cef');
TRANSLATE('❄A❄BX❄DYZ❄','XYZ❄','CEF')
abcdef

TRIM#

Syntax

TRIM(<expr>)

Description

Removes leading and trailing spaces from a string.

View examples

Formula Editor Example

SQL Editor Example

Remove leading and trailing ❄ and - characters from a string:

select trim('❄-❄ABC-❄-', '❄-');
TRIM('❄-❄ABC-❄-', '❄-')
ABC

UPPER#

Syntax

UPPER(<expr>)

Description

Returns the input string expression with all characters converted to uppercase.

View examples

Formula Editor Example

SQL Editor Example

select v, upper(v) from lu;
v upper(v)
1č2Щ3ß4Ę!-?abc@ 1Č2Щ3SS4Ę!-?ABC@
AaBbCcDdEeFfGgHhIiJj AABBCCDDEEFFGGHHIIJJ
KkLlMmNnOoPpQqRrSsTt KKLLMMNNOOPPQQRRSSTT
UuVvWwXxYyZz UUVVWWXXYYZZ
[NULL] [NULL]