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

Given the following table:

N_NAME
ALGERIA
ARGENTINA
BRAZIL
CANADA
EGYPT

Applied formula:

ASCII(N_NAME)

Output:

N_NAME_LENGTH
65.0
65.0
66.0
67.0
69.0

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

BIT_LENGTH(<string_or_binary>)

Description

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

View examples

Formula Editor Example

Given the following table:

N_NAME
ALGERIA
ARGENTINA
BRAZIL
CANADA
EGYPT

Applied formula:

BIT_LENGTH(N_NAME)

Output:

N_NAME_LENGTH
56.0
72.0
48.0
48.0
40.0

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

Given the following table:

COLOR_1 COLOR_2
blue and red red
blue and red green

Applied formula:

ENDSWITH(COLOR_1, COLOR_2)

Output:

COLOR_ENDSWITH
TRUE
FALSE

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

Given the following table:

NAME
first date
SECOND DATE
third Date

Applied formula:

BIT_LENGTH(NAME)

Output:

NAME_INITCAP
First Date
Second Date
Third Date

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

Given the following table:

N_NAME
ALGERIA
ARGENTINA
BRAZIL
CANADA
EGYPT

Applied formula:

LEFT( N_NAME , 3 )

Output:

N_NAME
ALG
ARG
BRA
CAN
EGY

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

Given the following table:

N_NAME
ALGERIA
ARGENTINA
BRAZIL
CANADA
EGYPT

Applied formula:

LENGTH(N_NAME)

Output:

N_NAME_LENGTH
7.0
9.0
6.0
6.0
5.0

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

Given the following table:

N_NAME
ALGERIA
ARGENTINA
BRAZIL
CANADA
EGYPT

Applied formula:

LOWER(N_NAME)

Output:

N_NAME_LOWER
algeria
argentina
brazil
canada
egypt

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

Given the following table:

C_NAME
Customer#000000001
Customer#000000002
Customer#000000003
Customer#000000004

Applied formula:

```LTRIM(C_NAME,'Customer')

Output:

|C_NAME_LTRIM|
|-|
|#000000001|
|#000000002|
|#000000003|
|#000000004|

**SQL Editor Example**

Remove leading 0 and # characters from a string

```sql
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

Given the following table:

N_NAME
FRANCE
GERMANY
INDIA

Applied formula:

OCTET_LENGTH(N_NAME)

Output:

N_NAME_OCTET_LENGTH
6.0
7.0
5.0

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

Given the following table:

COLUMN_1 COLUMN_2
dan dance
be baby

Applied formula:

POSITION(COLUMN_1 IN COLUMN_2)

Output:

COLUMN_POSITION
1.0
0.0

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

Given the following table:

N_NAME
ALGERIA
ARGENTINA
BRAZIL
CANADA
EGYPT

Applied formula:

REPEAT(N_NAME, 2)

Output:

N_NAME_REPEAT
AlgeriaAlgeria
ArgentinaArgentina
BrazilBrazil
CanadaCanada
EgyptEgypt

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

Given the following table:

N_NAME
ALGERIA
ARGENTINA
BRAZIL
CANADA
EGYPT

Applied formula:

REVERSE(N_NAME)

Output:

N_NAME_REVERSE
AIREGLA
ANITNEGRA
LIZARB
ADANAC
TPYGE

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

Given the following table:

P_NAME
yellow hot rose blue green
ellow pale blanched gainsboro moccasin
metallic rosy gainsboro dark spring
purple cream puff royal chocolate
turquoise floral papaya steel blanched

Applied formula:

RIGHT(P_NAME, 7)

Output:

P_NAME_RIGHT
e green
occasin
spring
ocolate
lanched

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

Given the following table:

L_EXTENDEDPRICE
41378.04
8616.48
33714.6

Applied formula:

RTRIM(L_EXTENDEDPRICE, 034)

Output:

L_EXTENDEDPRICE_RTRIM
41378.
8616.
33714.

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

Given the following table:

COLUMN_1
TRUCK
TRACK
MINNY
MINNI

Applied formula:

SOUNDEX( COLUMN_1 )

Output:

COLUMN_1_SOUNDEX
T620
T620
M308
M308

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

Given the following table:

DATE
1994-11-05
1995-01-28
1994-12-14
1994-11-25

Applied formula:

STRTOK(DATE, '-', 2)

Output:

DATE_STRTOK
11
01
12
11

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

Given the following table:

P_NAME
yellow hot rose blue green
yellow pale blanched gainsboro moccasin
metallic rosy gainsboro dark spring
purple cream puff royal chocolate

Applied formula:

TRANSLATE(P_NAME, 'e', 'o')

Output:

P_NAME_TRANSLATE
yollow hot roso bluo groon
yollow palo blanchod gainsboro moccasin
motallic rosy gainsboro dark spring
purplo croam puff royal chocolato

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

Given the following table:

P_NAME
yellow hot rose blue green
yellow pale blanched gainsboro moccasin
metallic rosy gainsboro dark spring
purple cream puff royal chocolate

Applied formula:

TRIM(P_NAME)

Output:

P_NAME_TRANSLATE
yellow hot rose blue green
yellow pale blanched gainsboro moccasin
metallic rosy gainsboro dark spring
purple cream puff royal chocolate

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

Given the following table:

P_NAME
yellow hot rose blue green
yellow pale blanched gainsboro moccasin
metallic rosy gainsboro dark spring
purple cream puff royal chocolate

Applied formula:

UPPER(P_NAME)

Output:

P_NAME_TRANSLATE
YELLOW HOT ROSE BLUE GREEN
YELLOW PALE BLANCHED GAINSBORO MOCCASIN
METALLIC ROSY GAINSBORO DARK SPRING
PURPLE CREAM PUFF ROYAL CHOCOLATE

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]