Arithmetic operators

Operators

Translation for Arithmetic Operators

Conversion Table

Redshift	Snowflake	Comments
+/- (positive and negative sign/operator)	+/-	Fully supported by Snowflake
^ (exponentiation)	POWER	Fully supported by Snowflake
* (multiplication)	*	Fully supported by Snowflake
/ (division)	/	Redshift division between integers always returns integer value, FLOOR function is added to emulate this behavior.
% (modulo)	%	Fully supported by Snowflake
+ (addition)	+ and ||	Fully supported by Snowflake. When string are added, it is transformed to a concat.
- (subtraction)	-	Fully supported by Snowflake
@ (absolute value)	ABS	Fully supported by Snowflake
|/ (square root)	SQRT	Fully supported by Snowflake
||/ (cube root)	CBRT	Fully supported by Snowflake

Sample Source Patterns

Addition, Subtraction, Positive & Negative

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE test_math_operations (
    base_value DECIMAL(10, 2),
    multiplier INT,
    divisor INT,
    description VARCHAR(100),
    created_at TIMESTAMP,
    category VARCHAR(50)
);


INSERT INTO test_math_operations (base_value, multiplier, divisor, description, created_at, category)
VALUES
(100.50, 2, 5, 'Basic test', '2024-12-01 10:30:00', 'Type A'),
(250.75, 3, 10, 'Complex operations', '2024-12-02 15:45:00', 'Type B'),
(-50.25, 5, 8, 'Negative base value', '2024-12-03 20:00:00', 'Type C'),
(0, 10, 2, 'Zero base value', '2024-12-04 09:15:00', 'Type D');


SELECT +base_value AS positive_value,
       -base_value AS negative_value,
       (base_value + multiplier - divisor) AS add_sub_result,
       created_at + INTERVAL '1 day' AS next_day,
       created_at - INTERVAL '1 hour' AS one_hour_before,
       description + category as string_sum,
       base_value + '5' as int_string_sum,
       '5' + base_value as string_int_sum
FROM test_math_operations;

Results

positive_value	negative_value	add_sub_result	next_day	one_hour_before	string_sum	int_string_sum	string_int_sum
100.50	-100.50	97.50	2024-12-02 10:30:00.000000	2024-12-01 09:30:00.000000	Basic testType A	105.5	105.5
250.75	-250.75	243.75	2024-12-03 15:45:00.000000	2024-12-02 14:45:00.000000	Complex operationsType B	255.75	255.75
-50.25	50.25	-53.25	2024-12-04 20:00:00.000000	2024-12-03 19:00:00.000000	Negative base valueType C	-45.25	-45.25
0.00	0.00	8.00	2024-12-05 09:15:00.000000	2024-12-04 08:15:00.000000	Zero base valueType D	5	5

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE test_math_operations (
    base_value DECIMAL(10, 2),
    multiplier INT,
    divisor INT,
    description VARCHAR(100),
    created_at TIMESTAMP,
    category VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "12/16/2024",  "domain": "test" }}';


INSERT INTO test_math_operations (base_value, multiplier, divisor, description, created_at, category)
VALUES
(100.50, 2, 5, 'Basic test', '2024-12-01 10:30:00', 'Type A'),
(250.75, 3, 10, 'Complex operations', '2024-12-02 15:45:00', 'Type B'),
(-50.25, 5, 8, 'Negative base value', '2024-12-03 20:00:00', 'Type C'),
(0, 10, 2, 'Zero base value', '2024-12-04 09:15:00', 'Type D');


SELECT +base_value AS positive_value,
       -base_value AS negative_value,
       (base_value + multiplier - divisor) AS add_sub_result,
       created_at + INTERVAL '1 day' AS next_day,
       created_at - INTERVAL '1 hour' AS one_hour_before,
       description || category as string_sum,
       base_value + '5' as int_string_sum,
       '5' + base_value as string_int_sum
FROM
       test_math_operations;

Results

positive_value	negative_value	add_sub_result	next_day	one_hour_before	string_sum	int_string_sum	string_int_sum
100.5	-100.5	97.5	2024-12-02 10:30:00	2024-12-01 09:30:00	Basic testType A	105.5	105.5
250.75	-250.75	243.75	2024-12-03 15:45:00	2024-12-02 14:45:00	Complex operationsType B	255.75	255.75
-50.25	50.25	-53.25	2024-12-04 20:00:00	2024-12-03 19:00:00	Negative base valueType C	-45.25	-45.25
0	0	8	2024-12-05 09:15:00	2024-12-04 08:15:00	Zero base valueType D	5	5

Exponentiation, multiplication, division & modulo

Input Code:

Redshift

IN -> Redshift_02.sql

CREATE TABLE test_math_operations (
    base_value DECIMAL(10, 2),
    multiplier INT,
    divisor INT,
    mod_value INT,
    exponent INT
);

INSERT INTO test_math_operations (base_value, multiplier, divisor, mod_value, exponent)
VALUES
(100.50, 2, 5, 3, 2),
(250.75, 3, 10, 7, 3),
(-50.25, 5, 8, 4, 4),
(0, 10, 2, 1, 5);

SELECT
    base_value ^ exponent AS raised_to_exponent,
    (base_value * multiplier) AS multiplied_value,
    (base_value / divisor) AS divided_value,
    base_value::int / divisor as int_division,
    (mod_value % 2) AS modulo_result,
    (base_value + multiplier - divisor) AS add_sub_result,
    (base_value + (multiplier * (divisor - mod_value))) AS controlled_eval
FROM
    test_math_operations;

Results

raised_to_exponent	multiplied_value	divided_value	int_division	modulo_result	add_sub_result	controlled_eval
10100.25	201	20.1	20	1	97.5	104.5
15766047.296875	752.25	25.075	25	1	243.75	259.75
6375940.62890625	-251.25	-6.28125	-6	0	-53.25	-30.25
0	0	0	0	1	8	10

Output Code:

Snowflake

OUT -> Redshift_02.sql

CREATE TABLE test_math_operations (
    base_value DECIMAL(10, 2),
    multiplier INT,
    divisor INT,
    mod_value INT,
    exponent INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "12/10/2024",  "domain": "test" }}';

INSERT INTO test_math_operations (base_value, multiplier, divisor, mod_value, exponent)
VALUES
(100.50, 2, 5, 3, 2),
(250.75, 3, 10, 7, 3),
(-50.25, 5, 8, 4, 4),
(0, 10, 2, 1, 5);

SELECT
    POWER(
    base_value, exponent) AS raised_to_exponent,
    (base_value * multiplier) AS multiplied_value,
    (base_value / divisor) AS divided_value,
    FLOOR(
    base_value::int / divisor) as int_division,
    (mod_value % 2) AS modulo_result,
    (base_value + multiplier - divisor) AS add_sub_result,
    (base_value + (multiplier * (divisor - mod_value))) AS controlled_eval
FROM
    test_math_operations;

Results

raised_to_exponent	multiplied_value	divided_value	int_division	modulo_result	add_sub_result	controlled_eval
10100.25	201	20.1	20	1	97.5	104.5
15766047.2969	752.25	25.075	25	1	243.75	259.75
6375940.6289	-251.25	-6.2812	-7	0	-53.25	-30.25
0	0	0	0	1	8	10

Absolute value, Square root and Cube root

Input Code:

Redshift

IN -> Redshift_02.sql

CREATE TABLE unary_operators
(
    col1 INTEGER,
    col2 INTEGER
);

INSERT INTO unary_operators VALUES
(14, 10),
(-8, 8),
(975, 173),
(-1273, 187);

SELECT
|/ col2 AS square_root,
||/ col1 AS cube_root,
@ col1 AS absolute_value
FROM unary_operators;

Results

+-------------------+--------------------+--------------+
|square_root        |cube_root           |absolute_value|
+-------------------+--------------------+--------------+
|3.1622776601683795 |2.4101422641752306  |14            |
|2.8284271247461903 |-2                  |8             |
|13.152946437965905 |9.915962413403873   |975           |
|13.674794331177344 |-10.837841647592736 |1273          |
+-------------------+--------------------+--------------+

Output Code:

Snowflake

OUT -> Redshift_02.sql

CREATE TABLE unary_operators
(
    col1 INTEGER,
    col2 INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "12/17/2024",  "domain": "test" }}';

INSERT INTO unary_operators
VALUES
(14, 10),
(-8, 8),
(975, 173),
(-1273, 187);

SELECT
    SQRT(col2) AS square_root,
    CBRT(col1) AS cube_root,
    ABS(col1) AS absolute_value
FROM
    unary_operators;

Results

+-------------+--------------+--------------+
|square_root  |cube_root     |absolute_value|
+-------------+--------------+--------------+
|3.16227766   |2.410142264   |14            |
|2.828427125  |-2            |8             |
|13.152946438 |9.915962413   |975           |
|13.674794331 |-10.837841648 |1273          |
+-------------+--------------+--------------+

Known Issues

1. In Snowflake, it is possible to use the unary operators +and - with string values, however in Redshift it is not valid.

Related EWIs

No related EWIs.