SQL ABS, MOD, POWER, SQRT: Math Functions (All Databases)

Overview

Core math functions for calculations in SQL. ABS, MOD, POWER, and SQRT work consistently across all major databases.

All Databases

-- ABS (absolute value):
SELECT ABS(-42);      -- 42
SELECT ABS(42);       -- 42

-- Use case: distance between two values
SELECT * FROM products
WHERE ABS(price - 100) < 10;  -- prices between 90-110

-- MOD (remainder / modulo):
SELECT MOD(10, 3);    -- 1 (10 / 3 = 3 remainder 1)
SELECT 10 % 3;        -- 1 (MySQL, PostgreSQL, SQL Server)

-- Even/odd check:
SELECT id, name FROM users WHERE MOD(id, 2) = 0;  -- even IDs

-- POWER (exponentiation):
SELECT POWER(2, 10);  -- 1024
SELECT POWER(10, 3);  -- 1000

-- SQRT (square root):
SELECT SQRT(144);     -- 12
SELECT SQRT(2);       -- 1.4142...

-- SIGN (returns -1, 0, or 1):
SELECT SIGN(-42);     -- -1
SELECT SIGN(0);       -- 0
SELECT SIGN(42);      -- 1

-- LOG / LN:
SELECT LOG(100);      -- varies by DB (natural or base-10)
SELECT LN(2.718);     -- ~1.0 (natural log)
SELECT LOG10(1000);   -- 3

-- RAND / RANDOM:
-- MySQL: SELECT RAND();         -- 0.0 to 1.0
-- PostgreSQL: SELECT RANDOM();  -- 0.0 to 1.0
-- SQL Server: SELECT RAND();
-- Random row: SELECT * FROM users ORDER BY RAND() LIMIT 1;

Skip the Syntax Lookup

Instead of memorizing ABS / MOD / POWER syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions