Functions

DATE

DATE(<expression-1> [ , <expression-2> ] )

The DATE function serves to convert a VARCHAR expression to a date format. The 2nd VARCHAR expression specifies the format (see Java 8 SimpleDateFormat). A possible time portion is ignored and only the date portion is used.

The result has the data type DATE.

The following format instructions should be used:

Letter	Meaning	Example	Result
y	Year	yyyy; yy	2011; 11
M	Month	MM;M	01; 1
d	Day	dd;d	04; 4

An example could look like this. If column d (type character string) has the value '2011-11-1', then

SELECT DATE(d, 'YYYY-M-d')
-- Result: 2011-11-01

---

Notes

There are also the format instructions Y (Week Year) and D (Day in Year), which should generally not be used.

Y uses the calendar week in which a given date falls to determine the year. Thus December 29, 1997 falls in calendar week 1 of 1998. The "week year" therefore gives 1998 for it. This produces a surprising but correct result:

SELECT DATE('1998-12-29', 'YYYY-MM-dd')
1997-12-29

D is used for the sequential number of a day. Together with the year number, this can be used to find a day by its number in the year, for example:

SELECT DATE('2021-189', 'yyyy-D')
2021-07-08

---

With the Excel driver it is currently not possible for date fields. The conversion must be performed after the data has been loaded as Number or String with the following formula: Date from 1/1/1900 - 2 + the date number from Excel. Example:

DATE '1900-01-01' - 1 - CASE WHEN datum_aus_excel > 58 THEN 1 ELSE 0 END + datum_aus_excel

January 1, 1900 corresponds to date number 1. In Excel, February 29, 1900 is a leap day (to be compatible with Lotus 1-2-3). Therefore one more day must be subtracted if the date is after February 28, 1900.

Example

For the DATE function it is important to use the correct format (yyyy is not equal to YYYY). Here are the DateFormats again

SELECT DATE('2021-12-24', 'yyyy-MM-dd'), DATE('20211224', 'YYYYMMDD'), DATE('24.12.2021 18:30:10', 'DD.MM.YYYY HH:mm:ss');

Output

column_1	column_2	column_3
2021-12-24	2020-12-27	2020-12-27

LTRIM

LTRIM(<expression-1> [ , <expression-2> ] )

Deletion of leading characters in the 1st VARCHAR expression.

The 2nd VARCHAR expression is optional and specifies a set of characters to be deleted. If not specified, the set is limited to spaces.

Deletes at the beginning of the first expression all characters that are in this set, up to the first character outside the set.

The result has the data type VARCHAR.

Example

SELECT LTRIM('   stays'), LTRIM('!"$% &%$stays', ' !"$%&');

Output

column_1 column_2
-------- --------
stays stays

RTRIM

RTRIM(<expression-1> [ , <expression-2> ] )

Deletion of trailing characters in the 1st VARCHAR expression.

The 2nd VARCHAR expression is optional and specifies a set of characters to be deleted. If not specified, the set is limited to spaces.

Deletes at the end of the first expression all characters that are in this set, up to the first character outside the set.

The result has the data type VARCHAR.

Example

SELECT RTRIM('stays    '), RTRIM('stays!"$% &%$', ' !"$%&');

Output

column_1 column_2
-------- --------
stays stays

TRIM

TRIM(<expression>)

Deletion of leading and trailing "whitespace" characters in the VARCHAR expression.

The function uses the java trim function (see Java 8 trim).

All characters in the ASCII code less than or equal to space are considered whitespace.

The result has the data type VARCHAR.

Example

SELECT TRIM('  stays    '), TRIM(CHR(9) || ' stays  ' || CHR(13) || CHR(10));

Output

column_1 column_2
-------- --------
stays stays

LOWER

LOWER(<expression>)

Convert all characters in the VARCHAR expression to lowercase.

The result has the data type VARCHAR.

Example

SELECT LOWER('Aa123'), lower('MiXeD');

Output

column_1 column_2
-------- --------
aa123 mixed

UPPER

UPPER(<expression>)

Convert all characters in the VARCHAR expression to uppercase.

The result has the data type VARCHAR.

Example

SELECT Upper('Aa123'), UPPER('MiXeD');

Output

column_1 column_2
-------- --------
AA123 MIXED

BIGINT

BIGINT(<expression>)

Convert the VARCHAR expression to a numeric, integer value.

Uses java Long.valueOf() and throws a runtime error if the string does not match the number format.

The result has the data type BIGINT.

Example

SELECT 1*'123';

Output

Implicit Conversion not yet implemented

SELECT 1*BIGINT('123');

Output

column_1
--------
123

SELECT 1*BIGINT('A123');

Output

java.lang.NumberFormatException: For input string: "A123"

DOUBLE

DOUBLE(<expression>)

Convert the VARCHAR expression to a numeric decimal value.

Uses java Double.valueOf() and throws a runtime error if the string does not match the number format.

The result has the data type DOUBLE.

Example

SELECT 9*'3.14159';

Output

Implicit Conversion not yet implemented

SELECT 9*'DOUBLE(3.14159)';

Output

column_1
--------
28.27431

SELECT 1*DOUBLE('A123');

Output

java.lang.NumberFormatException: For input string: "A123"

ASCII

ASCII(<expression>)

Convert the first character in the VARCHAR expression to the ASCII value.

The result has the data type BIGINT.

Example

SELECT ASCII('A'), ascii('ABC');

Output

column_1 column_2
-------- --------
65 65

LENGTH

LENGTH(<expression>)

Determine the length of a VARCHAR expression.

The result has the data type BIGINT.

Example

SELECT LENGTH('A'), lengTH('ABC');

Output

column_1 column_2
-------- --------
1 3

CHR

CHR(<expression>)

Convert the numeric expression to the corresponding ASCII character

The result has the data type VARCHAR.

Example

SELECT CHR(65), chr(35);

Output

column_1 column_2
-------- --------
A #

LPAD

LPAD(<expression-1>, <expression-2> [ , <expression-3> ])

Pad the 1st VARCHAR expression on the left with the 3rd VARCHAR expression until the length of the 2nd BIGINT expression is reached.

The 3rd VARCHAR expression is optional. If not specified, padding is with spaces.

The result has the data type VARCHAR.

Example

SELECT LPAD('x', 10), LPAD('x', 10, '1'), LPAD('xy', 10, 'abc'), lPad('xxxxxxxxxx', 10, '1');

Output

column_1 column_2 column_3 column_4
-------- -------- -------- --------
         x 111111111x abcabcabxy xxxxxxxxxx

In the 3rd example the 3rd expression is first inserted as often as it fits completely. Then the rightmost characters that no longer fit as padding are truncated

RPAD

RPAD(<expression-1>, <expression-2> [ , <expression-3> ])

Pad the 1st VARCHAR expression on the right with the 3rd VARCHAR expression until the length of the 2nd BIGINT expression is reached.

The 3rd VARCHAR expression is optional. If not specified, padding is with spaces.

The result has the data type VARCHAR.

Example

SELECT RPAD('x', 10), RPAD('x', 10, '1'), RPAD('xy', 10, 'abc'), rPad('xxxxxxxxxx', 10, '1');

Output

column_1 column_2 column_3 column_4
-------- -------- -------- --------
x          x111111111 xyabcabcab xxxxxxxxxx

In the 3rd example the 3rd expression is first inserted as often as it fits completely. Then the rightmost characters that no longer fit as padding are truncated

SUBSTR

SUBSTR(<expression-1>, <expression-2> [ , <expression-3> ])

Output of parts of the 1st VARCHAR expression.

The 2nd BIGINT expression gives the start position (starting with 1) and the optional 3rd BIGINT expression the length. The 3rd BIGINT expression is optional. If not specified, the remainder from the start position is taken. If it is longer than the remaining length, the remainder from the start position is also returned.

The result has the data type VARCHAR.

Example

SELECT SUBSTR('12345678', 4), substr('12345678', 2, 4), SUBSTR('12345678', 8, 4);

Output

column_1 column_2 column_3
-------- -------- --------
45678    2345     8

SELECT SUBSTR('12345678', 9);

Output

[position=1:27] Index out of bounds

INSTR

INSTR(<expression-1>, <expression-2> [ , <expression-3> [ , <expression-4> ] ])

Search for the 2nd VARCHAR expression in the 1st VARCHAR expression.

If the 3rd BIGINT expression is specified, the search starts from the corresponding position. The additional 4th BIGINT expression specifies which occurrence of the 2nd VARCHAR expression is to be searched for.

If the search is not successful, the value 0 is returned.

The result has the data type Bigint.

Example

SELECT instr('12341234', '1'), INSTR('12341234', '21'), INSTR('12341234', '1', 6), INSTR('12341234', '1', 1, 2);

Output

column_1 column_2 column_3 column_4
-------- -------- -------- --------
1        0        0        5

REPLACE

REPLACE(<expression-1>, <expression-2>, <expression-3>)

In the 1st VARCHAR expression replace all occurrences of the 2nd VARCHAR expression with the 3rd VARCHAR expression.

The function uses the java replace function (see Java 8 replace).

The result has the data type VARCHAR.

Example

SELECT REPLACE('AAAdas bleibtAAAAA', 'A', 'B'), REPLACE('AAAdas bleibtAAAAA', 'B', ''), REPLACE('AAA', 'A', '');

Output

column_1 column_2 column_3
-------- -------- --------
BBBdas bleibtBBBBB AAAdas bleibtAAAAA

REGEXP_REPLACE

REGEXP_REPLACE(<expression-1>, <expression-2>, <expression-3>)

In the 1st VARCHAR expression, all matches of the regular expression given in the 2nd VARCHAR expression are replaced with the 3rd VARCHAR expression.

The function uses the java replaceAll function (see Java 8 replaceAll).

The result has the data type VARCHAR.

Example

SELECT REGEXP_REPLACE('AAAdas bleibtBUHZ', '[A-Z]', 'B'), REGEXP_REPLACE('AAAdas bleibtAAAAA', 'B', ''), REGEXP_REPLACE('AAA', '.', '');

Output

column_1 column_2 column_3
-------- -------- --------
BBBdas bleibtBBBB AAAdas bleibtAAAAA