Shape / columns · All operations

Change a column's type

Change data type, detect type, set metadata or role.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result

one row out per row in, with columns added or changed

Level

one clause of SQL

The DuckDB step

Cast with failures becoming null

SQL

CREATE TEMP TABLE result AS
SELECT x, TRY_CAST(x AS INTEGER) AS as_int, TRY_CAST(x AS DATE) AS as_date
FROM (VALUES ('42'), ('2026-03-01'), ('n/a')) t(x);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(as_int) = 1 AND count(as_date) = 1 AND max(as_int) = 42 AND count(*) FILTER (WHERE x = 'n/a' AND as_int IS NULL AND as_date IS NULL) = 1 FROM result)) THEN 'as expected' ELSE error('a value that does not parse did not become null') END AS verdict;

The statement builds its own data.
It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “a value that does not parse did not become null”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Detect column types from the data
Scan the values of each column and set it to the type its data fits.
Set a column's role
Mark a column as a key, label, measure, dimension or semantic feature type without changing its values.

Convert a column to a named type

Change the stored data type of a column, failing or giving null on values that do not convert.

Define a type or schema

Declare a named type, a type mapping or a schema that later steps use.

Set column metadata

Attach or edit a column's domain, value set, display format or plot styling without changing its values.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)

Auto Fieldread 2026-09-27
Scan every record and set each field to the smallest type and size that fits its data
Feature Typesread 2026-09-27
Detect or set the semantic feature type of each column

Apache DataFusion (3)

Other Functionsread 2026-09-27
12 functions: arrow_cast, arrow_typeof, get_field, version, input_file_name, …
Type coercionread 2026-09-27
Coerce operand and argument types implicitly so expressions type-check
cast /try_castread 2026-09-27
Convert a value to a type; try_cast yields NULL instead of an error

Apache NiFi (1)

Type Coercionread 2026-09-27
3 functions: toString, toNumber, toDecimal

Apache SeaTunnel (1)

Define Sink Typeread 2026-09-27
Set the storage type of named columns for a sink that creates its table automatically

AWS Glue (3)

IntToIpread 2026-09-27
Convert an integer to the IPv4 address it stands for, in a new column
IpToIntread 2026-09-27
Convert an IPv4 address to its integer value, in a new column
ResolveChoiceread 2026-09-27
Resolve a field that holds more than one type into a single type or into separate fields

Azure Data Factory (3)

Castread 2026-09-27
Change column data types, with type checking
Conversion functionsread 2026-09-27
24 functions: array, base64, base64ToBinary, base64ToString, binary, …
Conversion functionsread 2026-09-27
31 functions: ascii, char, decode, encode, isBitSet, …

dbt-utils (2)

Cast functionsread 2026-09-27
3 macros: cast, cast_bool_to_text, safe_cast
Data type functionsread 2026-09-27
8 macros emitting adapter type names or now: type_bigint, type_boolean, type_float, type_int, type_string

DuckDB (2)

CREATE TYPEread 2026-09-27
Define an enum, struct or alias type
Enum Functions (family)read 2026-09-27
The functions documented on the Enum Functions page

Duckle (1)

Cast / Convert Typeread 2026-09-25
change column data types

EasyMorph (1)

Convert data typeread 2026-09-27
Convert values between text, number and date types

Ibis (2)

Castread 2026-09-27
Convert a value to a named data type, raising on a bad value
TryCastread 2026-09-27
Convert a value to a named data type, giving null when it cannot

KNIME Analytics Platform (26)

Color Designer (Apply)read 2026-09-27
Attach an existing colour palette or gradient to column values or column names
Color Gradient Designerread 2026-09-27
Define a colour gradient for the values of a numeric column
Color Palette Designerread 2026-09-27
Define a colour palette for categorical values or column names
Column Auto Type Castread 2026-09-27
Convert string columns to numeric or date type when every value parses
DB Type Mapperread 2026-09-27
Change how database types map to KNIME types for downstream DB nodes
Domain Calculatorread 2026-09-27
Rescan data to refresh each column's possible-value list and min/max metadata
Double to Integerread 2026-09-27
Convert floating-point columns to integers
Edit Nominal Domainread 2026-09-27
Reorder or add possible values in a nominal column's domain metadata by hand
Edit Nominal Domain (Dictionary)read 2026-09-27
Add possible values to nominal column domains from a second table
Edit Numeric Domainread 2026-09-27
Set lower and upper domain bounds of numeric columns and act on values outside them
Image to Tableread 2026-09-27
Put an image port object into a one-cell table
Number Format Managerread 2026-09-27
Attach a display format to numeric columns without changing the stored values
Number to Stringread 2026-09-27
Convert numeric columns to strings
Number to String (PMML)read 2026-09-27
Convert numeric columns to strings and record the step in PMML
Path to Stringread 2026-09-27
Convert a path column to a string column
Path to URIread 2026-09-27
Generate a URI for each path in a column, in a format the file system supports
Shape Appenderread 2026-09-27
Attach an existing shape model to a column
Shape Managerread 2026-09-27
Assign plot marker shapes to the values of a nominal column
Size Appenderread 2026-09-27
Attach an existing size model to a column
Size Managerread 2026-09-27
Assign plot marker sizes from the values of a numeric column
String Format Managerread 2026-09-27
Attach a display format to string columns without changing the stored values
String to Numberread 2026-09-27
Convert string columns to numbers
String to Number (PMML)read 2026-09-27
Convert string columns to numbers and record the step in PMML
String to Pathread 2026-09-27
Convert a string column to a path column
String to SVGread 2026-09-27
Convert a string or XML column into SVG image cells
Table to Imageread 2026-09-27
Turn the image in a table's first row into an image port object

OpenRefine (3)

To dateread 2026-09-27
Convert cells that parse as dates to the date type; others keep their value and type
To numberread 2026-09-27
Convert cells that parse as numbers to the number type; others keep their value and type
To textread 2026-09-27
Convert cells to the text type

Pentaho Data Integration (2)

Annotate streamread 2026-09-27
Mark stream fields as measures, attributes or linked dimensions for the Streamlined Data Refinery
Shared dimensionread 2026-09-27
Define a dimension that models can share, for the Streamlined Data Refinery

Polars (5)

DataFrame.castread 2026-09-27
Change the data type of chosen columns, or of all columns
Expr manipulation/selection: type conversion (group)read 2026-09-27
4 expressions: cast, reinterpret, shrink_dtype, to_physical; change the data type of a column
Expr.ext (group)read 2026-09-27
2 expressions: storage, to; convert between an extension type and its storage type
Expr.str: parse numbers (group)read 2026-09-27
2 expressions: to_decimal, to_integer; parse strings into decimals, or integers in a given base
SQL functions: Types (group)read 2026-09-27
2 functions: CAST, TRY_CAST; SQL type conversion

Power Query M (6)

Logical functionsread 2026-09-27
3 functions: Logical.From, Logical.FromText, Logical.ToText
Table.AddKeyread 2026-09-27
Declare a set of columns as a key of the table, optionally the primary key
Table.ReplaceKeysread 2026-09-27
Replace the keys declared on the table
Table.ReplacePartitionKeyread 2026-09-27
Replace the partition key declared on the table
Table.TransformColumnTypesread 2026-09-27
Convert named columns to given types, parsing under an optional culture
Type functionsread 2026-09-27
24 functions: Type.TableSchema, Type.ForRecord, Type.NonNullable, Type.ReplaceFacets, Type.AddTableKey

RapidMiner Studio (15)

Addread 2026-09-27
Add a new allowed value to a nominal column's value set
Convert Type (In Database)read 2026-09-27
Change the data type of selected columns, executed in the database
Format Numbersread 2026-09-27
Format numeric columns as text using a number, currency or percent pattern
Guess Typesread 2026-09-27
Re-detect the value type of each column from its contents
Nominal to Textread 2026-09-27
Change nominal columns to the free-text type
Numerical to Polynominalread 2026-09-27
Change numeric columns to nominal, each distinct number becoming a category
Numerical to Realread 2026-09-27
Change numeric columns to the real (floating point) type
Parse Numbersread 2026-09-27
Parse numbers out of text columns, with configurable decimal and grouping characters
Real to Integerread 2026-09-27
Convert real columns to integers by rounding or truncating
Remap Binominalsread 2026-09-27
Set which values of a two-valued column are internally negative and positive
Remove Unused Valuesread 2026-09-27
Remove nominal values that no row uses from a column's value set
Set Meta Dataread 2026-09-27
Add or overwrite column metadata (names, types, roles) without touching the data
Set Positive Valueread 2026-09-27
Declare which value of a two-valued nominal column is the positive class
Set Roleread 2026-09-27
Assign a role (label, id, weight, prediction, regular) to columns
Text to Nominalread 2026-09-27
Change free-text columns to the nominal type

Tableau Prep (2)

Change Data Typeread 2026-09-27
Change the data type of a field
Type conversion functions (group)read 2026-09-27
8 functions: DATE, FLOAT, INT, MAKEDATE, STR; convert between types and build dates

Talend (2)

tConvertTyperead 2026-09-27
Convert columns from one Talend Java type to another
tSetDynamicSchemaread 2026-09-27
Define a dynamic schema that later components reuse to read columns unknown at design time

Trifacta (5)

Change column typeread 2026-09-27
Change the data type of a column
Lock typeread 2026-09-27
Lock a column to its current data type so type inference stops changing it
Settype Transformread 2026-09-27
Set the data type of a column; values are re-validated against the type
Type Functions (group)read 2026-09-27
15 functions: IFNULL, IFMISMATCHED, ISMISSING, VALID, PARSEINT; test and handle null, missing, mismatched; parse to a type
Unlock typeread 2026-09-27
Unlock a column's data type so it is inferred again

VisiData (29)

addcol-castread 2026-09-23
(vdsql) Add column casting current column inside the database query
key-colread 2026-09-23
Toggle current column as a key column (group-by, join key and x-axis role)
key-col-offread 2026-09-23
Unset current column as a key column
key-col-onread 2026-09-23
Set current column as a key column
key-off-selectedread 2026-09-23
Unset selected source columns as key columns
key-selectedread 2026-09-23
Set selected source columns as key columns
type-anyread 2026-09-23
Set type of current column to anytype (untyped)
type-any-selectedread 2026-09-23
From Columns Sheet, set type of selected source columns to anytype
type-currencyread 2026-09-23
Set type of current column to currency (float after stripping non-numeric characters)
type-currency-selectedread 2026-09-23
From Columns Sheet, set type of selected source columns to currency
type-dateread 2026-09-23
Set type of current column to date
type-date-selectedread 2026-09-23
From Columns Sheet, set type of selected source columns to date
type-datedeltaread 2026-09-23
Set type of current column to datedelta
type-datetimeread 2026-09-23
Set type of current column to date with a datetime display format
type-floatread 2026-09-23
Set type of current column to float
type-float-selectedread 2026-09-23
From Columns Sheet, set type of selected source columns to float
type-floatlocaleread 2026-09-23
Set type of current column to float parsed by the system locale
type-floatlocale-selectedread 2026-09-23
From Columns Sheet, set type of selected source columns to locale-parsed float
type-floatsiread 2026-09-23
Set type of current column to float with SI suffixes
type-floatsi-selectedread 2026-09-23
From Columns Sheet, set type of selected source columns to float with SI suffixes
type-intread 2026-09-23
Set type of current column to int
type-int-selectedread 2026-09-23
From Columns Sheet, set type of selected source columns to int
type-ipaddrread 2026-09-23
Set type of current column to IP address
type-ipnetread 2026-09-23
Set type of current column to IP network
type-lenread 2026-09-23
Set type of current column to vlen (length of the value)
type-len-selectedread 2026-09-23
From Columns Sheet, set type of selected source columns to vlen
type-soupstrread 2026-09-23
Set type of current column to a list of HTML elements
type-stringread 2026-09-23
Set type of current column to str
type-string-selectedread 2026-09-23
From Columns Sheet, set type of selected source columns to str

Yggdrasil Decision Forests (2)

Feature / Columnread 2026-09-27
Declare one input column: its semantic, vocabulary limits, discretisation bins and monotonic constraint
Semanticread 2026-09-27
Enum of 7 column meanings: NUMERICAL, CATEGORICAL, HASH, CATEGORICAL_SET, BOOLEAN, DISCRETIZED_NUMERICAL …

Background

Type conversion, type casting and type coercion are different ways of changing an expression from one data type to another.^[1] A type conversion may happen implicitly, that is automatically, or explicitly.^[1] In DuckDB, a cast can fail on a value: casting the string 'hello' to INTEGER throws a conversion error.^[2] DuckDB's TRY_CAST never throws an error, and instead returns NULL when a cast is not possible.^[2]

When to use it

Reach for a cast when a column's stored type stops you doing what its values mean: sorting dates as dates, adding up amounts, or joining two keys that should match. (Meridian's view) Not every cast is possible: DuckDB cannot convert an INTEGER to a DATE at all.^[2] Casting the value 999 to TINYINT in DuckDB raises an error, because 999 is too large to be represented by that type.^[3]

Changing a type is not the only way to change how a column is treated: KNIME's Number Format Manager attaches formatting to columns, changing only how values are displayed in views and not the data.^[4] DuckDB's CREATE TYPE statement defines a new data type in the catalog, such as an ENUM of named values, available to that DuckDB instance.^[5]

The method

DuckDB adds implicit casts only for some type combinations, generally where the cast cannot fail, so INTEGER can be cast to DOUBLE implicitly but DOUBLE cannot be cast to INTEGER that way.^[3] When DuckDB casts from VARCHAR to another type, it attempts to parse the text as the target type and can raise an error at runtime if it cannot.^[3]

The VARCHAR type is a universal target in DuckDB: any value of any type can always be cast to VARCHAR.^[3] In general, casting to VARCHAR in DuckDB is lossless, and a value can be cast back to its original type from its text.^[3] The NULL value can always be converted between types in DuckDB.^[3]

DuckDB casts a list element by element: casting an INTEGER[] list to VARCHAR[] casts each child INTEGER to VARCHAR and builds a new list.^[3] DuckDB casts a struct to another struct only when the two share at least one field, and fields in the target that the source lacks default to NULL.^[3] DuckDB's cast_to_type function casts an expression to the type of another column, which is mainly useful in macros that must keep their input's type.^[2]

Sources

[1]Type conversionsecondary source
[2]Casting – DuckDBdocumentation
[3]Typecasting – DuckDBdocumentation
[4]Number Format Managerdocumentation
[5]CREATE TYPE Statement – DuckDBdocumentation

Types it works with

FineType's registry gives 251 types a cast expression.

Type	Expression
Comma-Separated Values (Simple)	`CAST({col} AS VARCHAR)`
Pipe-Separated Values	`CAST({col} AS VARCHAR)`
Semicolon-Separated Values	`CAST({col} AS VARCHAR)`
Whitespace-Separated Values	`CAST({col} AS VARCHAR)`
Query String Parameters	`CAST({col} AS VARCHAR)`
CSV Record	`CAST({col} AS VARCHAR)`
HTML Content	`REGEXP_REPLACE({col}, '<[^>]+>', '', 'g')`
JSON Object	`CAST({col} AS JSON)`
JSON Array	`CAST({col} AS JSON)`
S-Expression	`CAST({col} AS VARCHAR)`
XML Document	`CAST({col} AS VARCHAR)`
YAML Document	`CAST({col} AS VARCHAR)`
Day of Week Name	`{col}`
Full Month Name	`{col}`
Periodicity	`{col}`
Calendar Year	`{col}::SMALLINT`
Abbreviated Month Day Year (no comma)	`strptime({col}, '%b %d %Y')::DATE`
Abbreviated Month Date	`strptime(regexp_replace({col}, '\bSept\b', 'Sep'), ['%b %d, %Y', '%b. %d, %Y', '%d %b %y', '%d %b. %y', '%d %b %Y', '%d %b. %Y'])::DATE`
Chinese Standard Date	`strptime(regexp_replace(regexp_replace(regexp_replace({col}, '年', '-'), '月', '-'), '日', ''), '%Y-%-m-%-d')::DATE`
Compact Date (DDMMYYYY)	`strptime({col}, '%d%m%Y')::DATE`
Compact Date (MMDDYYYY)	`strptime({col}, '%m%d%Y')::DATE`
Compact Year-Month (YYYYMM)	`strptime({col} \|\| '01', '%Y%m%d')::DATE`
Compact Date (YYYYMMDD)	`strptime({col}, '%Y%m%d')::DATE`
DMY Date (dash-separated)	`strptime({col}, '%d-%m-%Y')::DATE`
DMY Date with Abbreviated Month (dash-separated)	`strptime({col}, '%d-%b-%Y')::DATE`
DMY Date with Abbreviated Month (dash, 2-digit year)	`strptime({col}, '%d-%b-%y')::DATE`
DMY Date (dot-separated)	`strptime({col}, '%d.%m.%Y')::DATE`
DMY Short Date (dot, 2-digit year)	`strptime({col}, '%d.%m.%y')::DATE`
DMY Short Date (slash, 2-digit year)	`strptime({col}, '%d/%m/%y')::DATE`
DMY Date (slash-separated)	`strptime({col}, '%d/%m/%Y')::DATE`
DMY Date with Abbreviated Month (space-separated)	`strptime({col}, '%d %b %Y')::DATE`
DMY Date with Full Month (space-separated)	`strptime({col}, '%d %B %Y')::DATE`
Full Month Day Year (no comma)	`strptime({col}, '%B %d %Y')::DATE`
ISO Date	`strptime({col}, '%Y-%m-%d')::DATE`
ISO Week Date	`strptime({col} \|\| '-1', '%G-W%V-%u')::DATE`
Japanese Imperial Year (Long/Kanji)	CASE WHEN {col} LIKE '令和%' THEN make_date(2018 + CAST(regexp_extract({col}, '(\d+)年', 1) AS INT), CAST(regexp_extract({col}, '(\d+)月', 1) AS INT), CAST(regexp_extract({col}, '(\d+)日', 1) AS INT)) WHEN {col} LIKE '平成%' THEN make_date(1988 + CAST(regexp_extract({col}, '(\d+)年', 1) AS INT), CAST(regexp_extract({col}, '(\d+)月', 1) AS INT), CAST(regexp_extract({col}, '(\d+)日', 1) AS INT)) WHEN {col} LIKE '昭和%' THEN make_date(1925 + CAST(regexp_extract({col}, '(\d+)年', 1) AS INT), CAST(regexp_extract({col}, '(\d+)月', 1) AS INT), CAST(regexp_extract({col}, '(\d+)日', 1) AS INT)) WHEN {col} LIKE '大正%' THEN make_date(1911 + CAST(regexp_extract({col}, '(\d+)年', 1) AS INT), CAST(regexp_extract({col}, '(\d+)月', 1) AS INT), CAST(regexp_extract({col}, '(\d+)日', 1) AS INT)) WHEN {col} LIKE '明治%' THEN make_date(1867 + CAST(regexp_extract({col}, '(\d+)年', 1) AS INT), CAST(regexp_extract({col}, '(\d+)月', 1) AS INT), CAST(regexp_extract({col}, '(\d+)日', 1) AS INT)) END
Japanese Imperial Year (Short)	CASE WHEN {col}[1] = 'R' THEN make_date(2018 + CAST(split_part(substring({col}, 2), '/', 1) AS INT), CAST(split_part(substring({col}, 2), '/', 2) AS INT), CAST(split_part(substring({col}, 2), '/', 3) AS INT)) WHEN {col}[1] = 'H' THEN make_date(1988 + CAST(split_part(substring({col}, 2), '/', 1) AS INT), CAST(split_part(substring({col}, 2), '/', 2) AS INT), CAST(split_part(substring({col}, 2), '/', 3) AS INT)) WHEN {col}[1] = 'S' THEN make_date(1925 + CAST(split_part(substring({col}, 2), '/', 1) AS INT), CAST(split_part(substring({col}, 2), '/', 2) AS INT), CAST(split_part(substring({col}, 2), '/', 3) AS INT)) WHEN {col}[1] = 'T' THEN make_date(1911 + CAST(split_part(substring({col}, 2), '/', 1) AS INT), CAST(split_part(substring({col}, 2), '/', 2) AS INT), CAST(split_part(substring({col}, 2), '/', 3) AS INT)) WHEN {col}[1] = 'M' THEN make_date(1867 + CAST(split_part(substring({col}, 2), '/', 1) AS INT), CAST(split_part(substring({col}, 2), '/', 2) AS INT), CAST(split_part(substring({col}, 2), '/', 3) AS INT)) END
Julian Date	`strptime(concat('20', {col}), '%Y-%j')::DATE`
Korean Standard Date	`strptime(regexp_replace(regexp_replace(regexp_replace({col}, '년 ', '-'), '월 ', '-'), '일', ''), '%Y-%-m-%-d')::DATE`
Long Date with Full Month Name	`strptime({col}, '%B %d, %Y')::DATE`
MDY Date (dash-separated)	`strptime({col}, '%m-%d-%Y')::DATE`
MDY Short Date (slash, 2-digit year)	`strptime({col}, '%m/%d/%y')::DATE`
MDY Date (slash-separated)	`strptime({col}, '%m/%d/%Y')::DATE`
Abbreviated Month Year	`strptime('01 ' \|\| upper(substr({col}, 1, 1)) \|\| lower(substr({col}, 2, 2)) \|\| ' ' \|\| substr({col}, length({col}) - 3), '%d %b %Y')::DATE`
Full Month Year	`strptime('01 ' \|\| {col}, '%d %B %Y')::DATE`
Month/Year (slash-separated)	`CASE WHEN LENGTH({col}) = 5 THEN strptime('01/' \|\| SUBSTRING({col}, 1, 2) \|\| '/20' \|\| SUBSTRING({col}, 4, 2), '%d/%m/%Y')::DATE ELSE strptime('01/' \|\| {col}, '%d/%m/%Y')::DATE END`
Ordinal Date	`strptime({col}, '%Y-%j')::DATE`
Short Date (Day-Month-Year, 2-digit year)	`strptime({col}, '%d-%m-%y')::DATE`
Short Date (Month-Day-Year, 2-digit year)	`strptime({col}, '%m-%d-%y')::DATE`
Short Date (Year-Month-Day, 2-digit year)	`strptime({col}, '%y-%m-%d')::DATE`
Full Weekday with Abbreviated Month	`strptime({col}, ['%A, %b %d, %Y', '%A, %d %b %Y'])::DATE`
Weekday DMY Full (formal)	`strptime({col}, '%A, %d %B %Y')::DATE`
Full Weekday with Full Month Name	`strptime({col}, '%A, %B %d, %Y')::DATE`
Year-Month (ISO truncated)	`strptime({col} \|\| '-01', '%Y-%m-%d')::DATE`
YMD Date (dot-separated)	`strptime({col}, '%Y.%m.%d')::DATE`
YMD Date (slash-separated)	`strptime({col}, '%Y/%m/%d')::DATE`
ISO 8601 Duration	CAST(CASE WHEN REGEXP_MATCHES({col}, '^P\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}$') THEN REGEXP_EXTRACT({col}, '^P(\d{4})-', 1) \|\| ' years ' \|\| REGEXP_EXTRACT({col}, '^P\d{4}-(\d{2})-', 1) \|\| ' months ' \|\| REGEXP_EXTRACT({col}, '^P\d{4}-\d{2}-(\d{2})T', 1) \|\| ' days ' \|\| REGEXP_EXTRACT({col}, 'T(\d{2}:\d{2}:\d{2})$', 1) ELSE COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '([\d.,]+)Y', 1), ',', '.'), '') \|\| ' years ', '') \|\| COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '^-?P(?:[\d.,]+Y)?([\d.,]+)M', 1), ',', '.'), '') \|\| ' months ', '') \|\| COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '([\d.,]+)W', 1), ',', '.'), '') \|\| ' weeks ', '') \|\| COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '([\d.,]+)D', 1), ',', '.'), '') \|\| ' days ', '') \|\| COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '([\d.,]+)H', 1), ',', '.'), '') \|\| ' hours ', '') \|\| COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, 'T(?:[\d.,]+H)?([\d.,]+)M', 1), ',', '.'), '') \|\| ' minutes ', '') \|\| COALESCE(NULLIF(REPLACE(REGEXP_EXTRACT({col}, '([\d.,]+)S', 1), ',', '.'), '') \|\| ' seconds', '0 seconds') END AS INTERVAL)
Unix Timestamp (microseconds)	`to_timestamp({col}::BIGINT / 1000000)`
Unix Timestamp (milliseconds)	`to_timestamp({col}::BIGINT / 1000)`
Unix Timestamp (seconds)	`to_timestamp({col}::BIGINT)`
IANA Timezone Name	`{col}`
Timezone Abbreviation	`{col}`
UTC Offset	`{col}`
Fiscal Year	`CAST(regexp_extract({col}, 'FY(\d{2,4})', 1) AS VARCHAR)`
Calendar Quarter	`make_date(CAST(regexp_extract({col}, '(\d{4})', 1) AS INT), (CAST(regexp_extract({col}, 'Q([1-4])', 1) AS INT) - 1) * 3 + 1, 1)`
12-Hour Time (HH:MM AM/PM)	`strptime({col}, '%I:%M %p')::TIME`
24-Hour Time (HH:MM)	`strptime({col}, '%H:%M')::TIME`
12-Hour Time with AM/PM	`strptime({col}, '%I:%M:%S %p')::TIME`
24-Hour Time (HH:MM:SS)	`strptime({col}, '%H:%M:%S')::TIME`
ISO Time with Microseconds	`strptime({col}, '%H:%M:%S.%f')::TIME`
Common Log Format (Apache/Nginx)	`strptime({col}, '%d/%b/%Y:%H:%M:%S %z')`
C ctime / asctime Format	`strptime({col}, '%a %b %-d %H:%M:%S %Y')`
DMY Hour-Minute Datetime	`strptime({col}, '%d/%m/%Y %H:%M')`
European Dot Timestamp (DMY 24h)	`strptime({col}, '%d.%m.%Y %H:%M:%S')`
Dot YMD Timestamp (24h)	`strptime({col}, '%Y.%m.%d %H:%M:%S')`
Unix Epoch Nanoseconds	`make_timestamp_ns({col}::BIGINT)`
ISO 8601	`strptime({col}, '%Y-%m-%dT%H:%M:%SZ')`
ISO 8601 Compact	`strptime({col}, '%Y%m%dT%H%M%S')`
ISO 8601 with Microseconds and Offset	`strptime({col}, '%Y-%m-%dT%H:%M:%S.%f%z')`
ISO 8601 with Microseconds	`strptime({col}, '%Y-%m-%dT%H:%M:%S.%fZ')`
ISO 8601 with Milliseconds and Offset	`strptime({col}, '%Y-%m-%dT%H:%M:%S.%g%z')`
ISO 8601 with Milliseconds	`strptime({col}, '%Y-%m-%dT%H:%M:%S.%gZ')`
ISO 8601 with Timezone Offset	`CAST(regexp_replace(upper({col}), '([+-])([0-9]):([0-9]{2})$', '\10\2:\3') AS TIMESTAMPTZ)`
ISO Datetime with Microseconds (no timezone)	`strptime({col}, '%Y-%m-%dT%H:%M:%S.%f')`
ISO Datetime with Milliseconds (no timezone)	`strptime({col}, '%Y-%m-%dT%H:%M:%S.%g')`
ISO Datetime, minute precision (no timezone)	`strptime({col}, '%Y-%m-%dT%H:%M')`
ISO Datetime, second precision (no timezone)	`strptime({col}, '%Y-%m-%dT%H:%M:%S')`
ISO with Space Separator and Z	`strptime({col}, '%Y-%m-%d %H:%M:%SZ')`
MDY 12-Hour Datetime	`strptime({col}, '%m/%d/%Y %I:%M %p')`
MDY 24-Hour Datetime	`strptime({col}, '%m/%d/%Y %H:%M:%S')`
PostgreSQL Short Offset	`COALESCE(try_strptime({col} \|\| ':00', '%Y-%m-%d %H:%M:%S%z'), try_strptime({col} \|\| ':00', '%Y-%m-%d %H:%M:%S.%f%z'))`
RFC 2822	`strptime({col}, '%a, %d %b %Y %H:%M:%S %z')`
RFC 2822 with Ordinal Indicators	`strptime(regexp_replace({col}, '(\d+)(?:st\|nd\|rd\|th)', '\1'), '%d %b %Y %H:%M:%S %z')`
RFC 3339 (Space Separator)	`CAST(regexp_replace(upper({col}), ' ([+-]\|Z)', '\1') AS TIMESTAMPTZ)`
Slash YMD Timestamp (24h)	`strptime({col}, '%Y/%m/%d %H:%M:%S')`
SQL Timestamp with Microseconds	`strptime({col}, '%Y-%m-%d %H:%M:%S.%f')`
SQL Timestamp with Microseconds and Offset	`strptime({col}, '%Y-%m-%d %H:%M:%S.%f%z')`
SQL Timestamp with Milliseconds	`strptime({col}, '%Y-%m-%d %H:%M:%S.%g')`
SQL Minute-Precision Datetime	`strptime({col}, '%Y-%m-%d %H:%M')`
SQL Standard	`strptime({col}, '%Y-%m-%d %H:%M:%S')`
BSD Syslog (RFC 3164)	`strptime(CAST(YEAR(CURRENT_DATE) AS VARCHAR) \|\| ' ' \|\| {col}, '%Y %b %-d %H:%M:%S')`
ABA Routing Number	`CAST({col} AS VARCHAR)`
BSB Number	`CAST({col} AS VARCHAR)`
IBAN	`UPPER(CAST({col} AS VARCHAR))`
SWIFT/BIC	`UPPER(CAST({col} AS VARCHAR))`
Bitcoin Address	`CAST({col} AS VARCHAR)`
Ethereum Address	`LOWER(CAST({col} AS VARCHAR))`
Currency Amount (US Format)	`CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CASE WHEN {col} LIKE '(%' THEN '-' \|\| REPLACE(REPLACE({col}, '(', ''), ')', '') ELSE {col} END, '^[^0-9\-]+', ''), '[^0-9\.\-]', '', 'g'), ',', '', 'g'), '\s+', '', 'g') AS DECIMAL(18,2))`
Currency Amount (US Accounting)	`CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE({col}, '(', '-'), ')', ''), '$', ''), ',', ''), ' ', '') AS DECIMAL(18,2))`
Currency Amount (Swiss Apostrophe)	`CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, 'CHF\s?', ''), '[''\x{2019}]', '', 'g'), '\s+', '', 'g') AS DECIMAL(18,2))`
Currency Amount (ISO Code Prefix)	`CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '^[A-Z]{3}\s?', ''), ',', '', 'g') AS DECIMAL(18,2))`
Currency Amount (EU Format)	`CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[€\$£¥₹₩₿\p{Sc}]', '', 'g'), '\s+', '', 'g'), '\.', '', 'g'), ',', '.') AS DECIMAL(18,2))`
Currency Amount (EU Suffix)	`CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[€$£¥₹₩₿\p{Sc}]', '', 'g'), '\s+', '', 'g'), '\.', '', 'g'), ',', '.') AS DECIMAL(18,2))`
Currency Amount (Cryptocurrency)	`CAST(REGEXP_REPLACE({col}, '\s*[A-Z]{2,5}$', '') AS DECIMAL(18,8))`
Currency Amount (Indian Lakh/Crore)	`CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '^(₹\|Rs\.?)\s*', ''), ',', '', 'g') AS DECIMAL(18,2))`
Currency Amount (Multi-Character Symbol)	`CAST(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '^[^0-9-]+\|[^0-9]+$\|[\s\x{A0}\x{202F}]', '', 'g'), '[.,]([0-9]{1,2})$', 'D\1'), '[.,]', '', 'g'), 'D', '.') AS DECIMAL(18,2))`
Currency Amount (Trailing Negative)	`CAST(CASE WHEN {col} LIKE '%-' OR {col} LIKE '%CR' THEN '-' \|\| REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[-]$\|\s(CR\|DR)$', ''), '[\$€£¥₹]', ''), ',', '', 'g') ELSE REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[-]$\|\s(CR\|DR)$', ''), '[\$€£¥₹]', ''), ',', '', 'g') END AS DECIMAL(18,2))`
Currency Amount (Zero-Decimal Currency)	`CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[¥₩\p{Sc}]', '', 'g'), ',', '', 'g') AS INTEGER)`
Currency Amount (Space-Separated Thousands)	`CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[€$£¥₹₩₿\p{Sc}]', '', 'g'), '[\s\x{00A0}]+', '', 'g'), ',', '.'), '\.$', '') AS DECIMAL(18,2))`
Currency Code (ISO 4217)	`UPPER(CAST({col} AS VARCHAR))`
Currency Symbol	`CAST({col} AS VARCHAR)`
Credit Card Number	`CAST({col} AS VARCHAR)`
Basis Points	`CAST(REGEXP_REPLACE({col}, '\s*(bps\|bp)$', '') AS DECIMAL(18,4)) / 10000`
Financial Yield / Return	`CAST(REGEXP_REPLACE({col}, '%$', '') AS DECIMAL(10,4)) / 100`
CUSIP	`CAST({col} AS VARCHAR)`
FIGI	`UPPER(CAST({col} AS VARCHAR))`
ISIN	`CAST({col} AS VARCHAR)`
LEI	`CAST({col} AS VARCHAR)`
SEDOL	`CAST({col} AS VARCHAR)`
Stock Ticker	`CAST({col} AS VARCHAR)`
Full Address	`CAST({col} AS VARCHAR)`
Postal Code	`CAST({col} AS VARCHAR)`
Street Name	`CAST({col} AS VARCHAR)`
Street Suffix	`CAST({col} AS VARCHAR)`
International Calling Code	`CAST({col} AS VARCHAR)`
Coordinate Pair (Lat, Lon)	`STRUCT_PACK(lat := CAST(REGEXP_EXTRACT({col}, '^(-?[0-9]+(?:\.[0-9]+)?)', 1) AS DOUBLE), lon := CAST(REGEXP_EXTRACT({col}, '(-?[0-9]+(?:\.[0-9]+)?)$', 1) AS DOUBLE))`
Degrees Minutes Seconds	`CAST({col} AS VARCHAR)`
Geohash	`CAST({col} AS VARCHAR)`
Latitude	`CAST({col} AS DOUBLE)`
Longitude	`CAST({col} AS DOUBLE)`
Military Grid Reference System	`CAST({col} AS VARCHAR)`
Plus Code (Open Location Code)	`CAST({col} AS VARCHAR)`
Well-Known Text Geometry	`CAST({col} AS VARCHAR)`
H3 Hex Index	`CAST({col} AS VARCHAR)`
City Name	`CAST({col} AS VARCHAR)`
Continent Name	`CAST({col} AS VARCHAR)`
Country Name	`CAST({col} AS VARCHAR)`
Country Code	`UPPER(CAST({col} AS VARCHAR))`
Region / State	`CAST({col} AS VARCHAR)`
State/Province Code	`UPPER(CAST({col} AS VARCHAR))`
HS Tariff Code	`CAST({col} AS VARCHAR)`
IATA Airport Code	`UPPER(CAST({col} AS VARCHAR))`
ICAO Airport Code	`UPPER(CAST({col} AS VARCHAR))`
Shipping Container Code (ISO 6346)	`CAST({col} AS VARCHAR)`
UN/LOCODE	`UPPER(CAST({col} AS VARCHAR))`
ORCID	`CAST({col} AS VARCHAR)`
EAN	`CAST({col} AS VARCHAR)`
ISBN	`CAST({col} AS VARCHAR)`
ISRC	`CAST({col} AS VARCHAR)`
ISSN	`CAST({col} AS VARCHAR)`
UPC	`CAST({col} AS VARCHAR)`
Australian Business Number	`CAST({col} AS VARCHAR)`
US Employer Identification Number	`CAST({col} AS VARCHAR)`
EU VAT Number	`CAST({col} AS VARCHAR)`
Indian PAN	`CAST({col} AS VARCHAR)`
US Social Security Number	`CAST({col} AS VARCHAR)`
Vehicle Identification Number	`CAST({col} AS VARCHAR)`
NAICS Industry Code	`CAST({col} AS VARCHAR)`
CPT Procedure Code	`CAST({col} AS VARCHAR)`
DEA Number	`CAST({col} AS VARCHAR)`
HCPCS Level II Code	`CAST({col} AS VARCHAR)`
ICD-10 Diagnosis Code	`CAST({col} AS VARCHAR)`
LOINC Code	`CAST({col} AS VARCHAR)`
NDC (National Drug Code)	`CAST({col} AS VARCHAR)`
NPI (National Provider Identifier)	`CAST({col} AS VARCHAR)`
Blood Type	`UPPER(CAST({col} AS VARCHAR))`
Email Address	`LOWER(CAST({col} AS VARCHAR))`
Email with Display Name	`CAST({col} AS VARCHAR)`
First Name	`CAST({col} AS VARCHAR)`
Full Name	`CAST({col} AS VARCHAR)`
Gender	`CAST({col} AS VARCHAR)`
Gender Code	`CAST(UPPER({col}) AS VARCHAR)`
Height	`CASE WHEN UPPER({col}) LIKE '%FT%' OR {col} LIKE '%''%' THEN (CAST(REGEXP_EXTRACT({col}, '^([0-9]+)', 1) AS DOUBLE) * 30.48) + (CAST(COALESCE(NULLIF(REGEXP_EXTRACT({col}, '''([0-9]+)', 1), ''), '0') AS DOUBLE) * 2.54) WHEN UPPER({col}) LIKE '%IN%' THEN CAST(REGEXP_EXTRACT({col}, '^([0-9.]+)', 1) AS DOUBLE) * 2.54 ELSE CAST(REGEXP_EXTRACT({col}, '^([0-9.]+)', 1) AS DOUBLE) END`
Last Name	`CAST({col} AS VARCHAR)`
Password	`CAST({col} AS VARCHAR)`
Phone Number (E.164)	`CAST({col} AS VARCHAR)`
Phone Number	`CAST({col} AS VARCHAR)`
Username	`LOWER(CAST({col} AS VARCHAR))`
Weight	`CASE WHEN UPPER({col}) LIKE '%LB%' OR UPPER({col}) LIKE '%POUND%' THEN CAST(REGEXP_EXTRACT({col}, '^([0-9.]+)', 1) AS DOUBLE) / 2.20462 ELSE CAST(REGEXP_EXTRACT({col}, '^([0-9.]+)', 1) AS DOUBLE) END`
Binary Boolean (0/1)	`CAST({col} AS BOOLEAN)`
Boolean Initials (T/F, Y/N)	`CAST({col} AS BOOLEAN)`
Boolean Terms (True/False, Yes/No)	`CAST(CASE WHEN lower({col}) IN ('on', 'enabled', 'active') THEN 'true' WHEN lower({col}) IN ('off', 'disabled', 'inactive') THEN 'false' ELSE {col} END AS BOOLEAN)`
Ordinal Value	`CAST({col} AS VARCHAR)`
Excel Number Format String	`CAST({col} AS VARCHAR)`
File Extension	`LOWER(REGEXP_REPLACE({col}, '^\.*', ''))`
File Size	CASE REGEXP_EXTRACT({col}, '([A-Za-z]+)$', 1) WHEN '' THEN 1 WHEN 'B' THEN 1 WHEN 'kB' THEN 1000 WHEN 'KB' THEN 1000 WHEN 'MB' THEN 1000000 WHEN 'GB' THEN 1000000000 WHEN 'TB' THEN 1000000000000 WHEN 'PB' THEN 1000000000000000 WHEN 'K' THEN 1024 WHEN 'KiB' THEN 1024 WHEN 'M' THEN 1048576 WHEN 'MiB' THEN 1048576 WHEN 'G' THEN 1073741824 WHEN 'GiB' THEN 1073741824 WHEN 'T' THEN 1099511627776 WHEN 'TiB' THEN 1099511627776 WHEN 'P' THEN 1125899906842624 WHEN 'PiB' THEN 1125899906842624 END * CAST(REGEXP_EXTRACT({col}, '^([0-9.]+)', 1) AS DOUBLE)
MIME Type	`LOWER(CAST({col} AS VARCHAR))`
Color (Hexadecimal)	`UPPER(CAST({col} AS VARCHAR))`
Color (HSL)	`CAST({col} AS VARCHAR)`
Color (RGB)	`CAST({col} AS VARCHAR)`
Alphanumeric Identifier	`CAST({col} AS VARCHAR)`
Increment / Counter	`CAST({col} AS BIGINT)`
Numeric Code	`CAST({col} AS VARCHAR)`
UUID	`CAST({col} AS UUID)`
Decimal Number	`CAST({col} AS DOUBLE)`
Decimal Number (Comma Separator)	`CAST(REPLACE(REPLACE({col}, '.', ''), ',', '.') AS DOUBLE)`
Integer Number	`CAST({col} AS BIGINT)`
Percentage	`CAST(REGEXP_EXTRACT({col}, '^%?(-?[0-9]+(\.[0-9]+)?)', 1) \|\| 'e-2' AS DOUBLE)`
Scientific Notation	`CAST({col} AS DOUBLE)`
SI-Prefix Number	CASE WHEN regexp_extract(regexp_replace({col}, '^[\\$€£+-]', ''), '[KkMmBbTt]$') IN ('K','k') THEN CAST(regexp_replace(regexp_replace({col}, '^[\\$€£+-]', ''), '[Kk]$', '') AS DOUBLE) * 1000 WHEN regexp_extract(regexp_replace({col}, '^[\\$€£+-]', ''), '[KkMmBbTt]$') IN ('M','m') THEN CAST(regexp_replace(regexp_replace({col}, '^[\\$€£+-]', ''), '[Mm]$', '') AS DOUBLE) * 1000000 WHEN regexp_extract(regexp_replace({col}, '^[\\$€£+-]', ''), '[KkMmBbTt]$') IN ('B','b') THEN CAST(regexp_replace(regexp_replace({col}, '^[\\$€£+-]', ''), '[Bb]$', '') AS DOUBLE) * 1000000000 WHEN regexp_extract(regexp_replace({col}, '^[\\$€£+-]', ''), '[KkMmBbTt]$') IN ('T','t') THEN CAST(regexp_replace(regexp_replace({col}, '^[\\$€£+-]', ''), '[Tt]$', '') AS DOUBLE) * 1000000000000 END
CAS Registry Number	`CAST({col} AS VARCHAR)`
DNA Sequence	`UPPER(CAST({col} AS VARCHAR))`
InChI Identifier	`CAST({col} AS VARCHAR)`
Measurement Unit	`LOWER(CAST({col} AS VARCHAR))`
Protein Sequence	`UPPER(CAST({col} AS VARCHAR))`
RNA Sequence	`UPPER(CAST({col} AS VARCHAR))`
SMILES Notation	`CAST({col} AS VARCHAR)`
Emoji	`CAST({col} AS VARCHAR)`
Entity Name	`CAST({col} AS VARCHAR)`
Plain Text	`CAST({col} AS VARCHAR)`
Single Word	`LOWER(CAST({col} AS VARCHAR))`
AWS ARN	`CAST({col} AS VARCHAR)`
S3 URI	`CAST({col} AS VARCHAR)`
DOI	`CAST({col} AS VARCHAR)`
IMEI	`CAST({col} AS VARCHAR)`
Locale Code	`CAST({col} AS VARCHAR)`
Qualified Name	`CAST({col} AS VARCHAR)`
Cryptographic Hash	`CAST({col} AS VARCHAR)`
JSON Web Token	`CAST({col} AS VARCHAR)`
URL-Safe Base64 Token	`CAST({col} AS VARCHAR)`
Calendar Version	`CAST({col} AS VARCHAR)`
Docker/OCI Image Reference	`CAST({col} AS VARCHAR)`
Version Number	`CAST({col} AS VARCHAR)`
Filename	`CAST({col} AS VARCHAR)`
Windows File Path	`CAST({col} AS VARCHAR)`
Snowflake ID	`epoch_ms(({col}::BIGINT >> 22) + 1288834974657)`
TSID	`CAST({col} AS VARCHAR)`
ULID	`CAST({col} AS VARCHAR)`
CIDR Notation	`CAST({col} AS VARCHAR)`
Data URI	`CAST({col} AS VARCHAR)`
Hostname	`CAST({col} AS VARCHAR)`
HTTP Method	`CAST({col} AS VARCHAR)`
IPv4 Address	`CAST({col} AS VARCHAR)`
IPv4 Address with Port	`CAST({col} AS VARCHAR)`
IPv6 Address	`CAST({col} AS VARCHAR)`
MAC Address	`CAST({col} AS VARCHAR)`
Email Message-ID	`CAST({col} AS VARCHAR)`
Top-Level Domain (TLD)	`CAST({col} AS VARCHAR)`
URL	`CAST({col} AS VARCHAR)`
URN	`CAST({col} AS VARCHAR)`
User Agent String	`CAST({col} AS VARCHAR)`

Used in

12 Protocols use this operation:

GLEIF — Legal Entity Identifiers, step load
California Housing dataset, step load
Census Income (Adult) dataset, step load
EDGAR ↔ GLEIF — Company-to-LEI Crosswalk, steps build_sec_entities, load, tier, package
Movies (vega-datasets) dataset, step load
Online Retail II dataset, steps retail_lines, retail_orders
Palmer Penguins dataset, step penguins
ScienceQA dataset, step load
SuperGPQA dataset, step corpus
NYC Yellow Taxi Trips dataset, step trips
SEC EDGAR — Company Tickers, step load
USGS Earthquakes 2025 dataset, step earthquakes