REMAP_INTS
Description
REMAP_INTS
allows you to map values of a column of type INT.
Syntax
REMAP_INTS ( table.column, [ old_value1, new_value1 ] , ... [, others_value ] )
The first parameter of REMAP_INTS
is an input column of type INT that provides the values that should be remapped. The second parameter is a list of one or more pairs of INT values that describe the mapping. Each occurrence of the first value of the pair will be remapped to the second value of the pair. If a value is remapped in multiple pairs, only the last pair is used. Finally, the operator accepts an optional INT value that will replace all values that are not remapped within the mapping. If this optional default replacement value is missing, all values not considered in the mapping will remain unchanged.
As the REMAP_INTS
operator is only applicable to columns of type INT, REMAP_VALUES provides a similar functionality for columns of type STRING.
You can achieve the same result with a CASE WHEN statement but REMAP_INTS
is faster and the syntax is more compact.
NULL handling
If the input value is NULL and it is not mapped to any other value, REMAP_INTS
will return NULL again.
Examples
[1] Simple example of REMAP_INTS, where all occurrences of integer 3 are mapped to 2: | ||||||||||||||
| ||||||||||||||
|
[2] In this example, 1 is remapped to NULL, and NULL values are mapped to 100. Note that the mappings are not applied incrementally; only the last matching mapping for each input row is applied: | ||||||||||||||
| ||||||||||||||
|
[3] In this example, | ||||||||||||||||||||||||||
| ||||||||||||||||||||||||||
|