Help Center/ Data Lake Insight/ SQL Syntax Reference (ME-Abu Dhabi Region)/ Flink SQL Syntax/ Built-In Functions/ Type Conversion Functions
Updated on 2023-03-06 GMT+08:00
View PDF
Share

Type Conversion Functions

Syntax

CAST(value AS type)

Syntax Description

This function is used to forcibly convert types.

Precautions

  • If the input is NULL, NULL is returned.
  • Flink jobs do not support the conversion of bigint to timestamp using CAST. You can convert it using to_timestamp or to_localtimestamp.

Example

Convert amount into a character string. The specified length of the string is invalid after the conversion.

insert into temp select cast(amount as VARCHAR(10)) from source_stream;

Common Type Conversion Functions

Table 1 Common type conversion functions

Function

Description

cast(v1 as varchar)

Converts v1 to a string. The value of v1 can be of the numeric type or of the timestamp, date, or time type.

cast (v1 as int)

Converts v1 to the int type. The value of v1 can be a number or a character.

cast(v1 as timestamp)

Converts v1 to the timestamp type. The value of v1 can be of the string, date, or time type.

cast(v1 as date)

Converts v1 to the date type. The value of v1 can be of the string or timestamp type.
  • cast(v1 as varchar)
    • Test statement 
      SELECT cast(content as varchar) FROM T1;
    • Test data and result
      Table 2 T1

      content (INT)

      varchar

      5

      "5"
  • cast (v1 as int)
    • Test statement 
      SELECT cast(content as int) FROM T1;
    • Test data and result
      Table 3 T1

      content (STRING)

      int

      "5"

      5
  • cast(v1 as timestamp)
    • Test statement 
      SELECT cast(content as timestamp) FROM T1;
    • Test data and result
      Table 4 T1

      content (STRING)

      timestamp

      "2018-01-01 00:00:01"

      1514736001000
  • cast(v1 as date)
    • Test statement 
      SELECT cast(content as date) FROM T1;
    • Test data and result
      Table 5 T1

      content (TIMESTAMP)

      date

      1514736001000

      "2018-01-01"

Detailed Sample Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
/** source **/
CREATE
SOURCE STREAM car_infos (cast_int_to_varchar int, cast_String_to_int string,
case_string_to_timestamp string, case_timestamp_to_date timestamp) WITH (
  type = "dis",
  region = "xxxxx",
  channel = "dis-input",
  partition_count = "1",
  encode = "json",
  offset = "13",
  json_config =
"cast_int_to_varchar=cast_int_to_varchar;cast_String_to_int=cast_String_to_int;case_string_to_timestamp=case_string_to_timestamp;case_timestamp_to_date=case_timestamp_to_date"
 
);
/** sink **/
CREATE
SINK STREAM cars_infos_out (cast_int_to_varchar varchar, cast_String_to_int
int, case_string_to_timestamp timestamp, case_timestamp_to_date date) WITH (
  type = "dis",
  region = "xxxxx",
  channel = "dis-output",
  partition_count = "1",
  encode = "json",
  offset = "4",
  json_config =
"cast_int_to_varchar=cast_int_to_varchar;cast_String_to_int=cast_String_to_int;case_string_to_timestamp=case_string_to_timestamp;case_timestamp_to_date=case_timestamp_to_date",
  enable_output_null="true"
);
/** Statistics on static car information**/
INSERT
INTO
  cars_infos_out
SELECT
  cast(cast_int_to_varchar as varchar),
  cast(cast_String_to_int as int),
  cast(case_string_to_timestamp as timestamp),
  cast(case_timestamp_to_date as date)
FROM
  car_infos;

Returned data

{"case_string_to_timestamp":1514736001000,"cast_int_to_varchar":"5","case_timestamp_to_date":"2018-01-01","cast_String_to_int":100}
Parent topic: Built-In Functions