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 |
| 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 |
| CANADA |
| 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 |
| 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 |
| 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 |
| 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
| N_NAME_1 |
|---|
| AUTOMOBILE |
| MACHINERY |
| BUILDING |
Applied formula:
LPAD(N_NAME_1, '10', '$')
Output:
| N_NAME_LPAD |
|---|
| 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, ' '),
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 |
| 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
| 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') |
|---|
| 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 |
RIGHT#
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
| N_NAME_1 |
|---|
| AUTOMOBILE |
| MACHINERY |
| BUILDING |
Applied formula:
RPAD(N_NAME_1, '10', '$')
Output:
| N_NAME_LPAD |
|---|
| AUTOMOBILE |
| MACHINERY$ |
| BUILDING$$ |
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
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:
| N_NAME_LPAD |
|---|
| 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
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] |