# 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
EGYPT

Applied formula:

``````ASCII(N_NAME)
``````

Output:

N_NAME_LENGTH
65
65
66
67
69

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
EGYPT

Applied formula:

``````BIT_LENGTH(N_NAME)
``````

Output:

N_NAME_LENGTH
56
72
48
48
40

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

N_NAME_1 N_NAME_2 N_NAME_3
AUTOMOBILE service
MACHINERY service
BUILDING service team

Applied formula:

``````CONCAT(N_NAME_1, N_NAME_2)
``````

Output:

N_NAME_concat
AUTOMOBILE service
MACHINERY service
BUILDING service team

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

Applied formula:

``````LENGTH(N_NAME)
``````

Output:

N_NAME_LENGTH
7
9
6
6
5

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
EGYPT

Applied formula:

``````LOWER(N_NAME)
``````

Output:

N_NAME_LOWER
algeria
argentina
brazil
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]

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

N_NAME_1
AUTOMOBILE
MACHINERY
BUILDING

Applied formula:

``````LPAD(N_NAME_1, '10', '\$')
``````

Output:

AUTOMOBILE
\$MACHINERY
§§BUILDING

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, ' '),
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

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

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

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
EGYPT

Applied formula:

``````REPEAT(N_NAME, 2)
``````

Output:

N_NAME_REPEAT
AlgeriaAlgeria
ArgentinaArgentina
BrazilBrazil
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

N_NAME_1
AUTOMOBILE
MACHINERY
VW AUTOMOBILE

Applied formula:

``````REPLACE(N_NAME_1, 'AUTO', 'fancy')
``````

Output:

N_NAME_REPLACE
fancyMOBILE
MACHINERY
VW fancyMOBILE

SQL Editor Example

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

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

Replace strings in a value with a specified replacement:

``````create or replace table replace_example(subject varchar(10), pattern varchar(10), replacement varchar(10));
``````
``````select subject, pattern, replacement, replace(subject, pattern, replacement) as new from replace_example;
``````
SUBJECT PATTERN REPLACEMENT NEW
snowman snow fire fireman

## 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
EGYPT

Applied formula:

``````REVERSE(N_NAME)
``````

Output:

N_NAME_REVERSE
AIREGLA
ANITNEGRA
LIZARB
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

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

N_NAME_1
AUTOMOBILE
MACHINERY
BUILDING

Applied formula:

``````RPAD(N_NAME_1, '10', '\$')
``````

Output:

AUTOMOBILE
MACHINERY\$
BUILDING\$\$

SQL Editor Example

``````select rpad('123.50', 20, '*-') from dual;
``````
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

Given the following table:

N_NAME_1
AUTOMOBILE Service
MACHINERY port
BUILDING entrance

Applied formula:

``````SUBSTRING(N_NAME_1, '3', '10')
``````

Output:

TOMOBILE S
CHINERY po
ILDING ent

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