CIEDE2000 implementation in SQL

This page presents a reference implementation of the CIEDE2000 color difference formula in SQL. If you wish to obtain an exact match with third-party implementations up to 10 decimal places, you may need to make some changes to the source code, including commenting and uncommenting a few lines, which can be applied automatically via the link below.

The ΔE2000 function in SQL

Let’s consider the more common and academic (Sharma, 2005) of the two formulations.

-- This function written in SQL is not affiliated with the CIE (International Commission on Illumination),
-- and is released into the public domain. It is provided "as is" without any warranty, express or implied.

DELIMITER //

-- Delete any function of the same name that already exists
DROP FUNCTION IF EXISTS ciede_2000 //

-- The classic CIE ΔE2000 implementation, which operates on two L*a*b* colors, and returns their difference.
-- "l" ranges from 0 to 100, while "a" and "b" are unbounded and commonly clamped to the range of -128 to 127.
CREATE FUNCTION ciede_2000(l_1 DOUBLE, a_1 DOUBLE, b_1 DOUBLE, l_2 DOUBLE, a_2 DOUBLE, b_2 DOUBLE)
RETURNS DOUBLE
DETERMINISTIC
NO SQL
BEGIN
	-- Working in SQL/PSM with the CIEDE2000 color-difference formula.
	-- k_l, k_c, k_h are parametric factors to be adjusted according to
	-- different viewing parameters such as textures, backgrounds...
	DECLARE k_l, k_c, k_h DOUBLE DEFAULT 1.0;
	DECLARE n, c_1, c_2, h_1, h_2, h_m, h_d, r_t, p, t, l, c, h DOUBLE;

	SET n = (SQRT(a_1 * a_1 + b_1 * b_1) + SQRT(a_2 * a_2 + b_2 * b_2)) * 0.5;
	SET n = n * n * n * n * n * n * n;
	-- A factor involving chroma raised to the power of 7 designed to make
	-- the influence of chroma on the total color difference more accurate.
	SET n = 1.0 + 0.5 * (1.0 - SQRT(n / (n + 6103515625.0)));
	-- Application of the chroma correction factor.
	SET c_1 = SQRT(a_1 * a_1 * n * n + b_1 * b_1);
	SET c_2 = SQRT(a_2 * a_2 * n * n + b_2 * b_2);
	-- atan2 is preferred over atan because it accurately computes the angle of
	-- a point (x, y) in all quadrants, handling the signs of both coordinates.
	SET h_1 = COALESCE(ATAN2(b_1, a_1 * n), 0);
	SET h_2 = COALESCE(ATAN2(b_2, a_2 * n), 0);
	IF h_1 < 0 THEN SET h_1 = h_1 + 2 * PI(); END IF;
	IF h_2 < 0 THEN SET h_2 = h_2 + 2 * PI(); END IF;
	SET n = ABS(h_2 - h_1);
	-- Cross-implementation consistent rounding.
	IF PI() - 1E-14 < n AND n < PI() + 1E-14 THEN SET n = PI(); END IF;
	-- When the hue angles lie in different quadrants, the straightforward
	-- average can produce a mean that incorrectly suggests a hue angle in
	-- the wrong quadrant, the next lines handle this issue.
	SET h_m = (h_1 + h_2) * 0.5;
	SET h_d = (h_2 - h_1) * 0.5;
	IF PI() < n THEN
		SET h_d = h_d + PI();
		-- 📜 Sharma’s formulation doesn’t use the next line, but the one after it,
		-- and these two variants differ by ±0.0003 on the final color differences.
		SET h_m = h_m + PI();
		-- SET h_m = h_m + CASE WHEN h_m < PI() THEN PI() ELSE -PI() END;
	END IF;
	SET p = 36.0 * h_m - 55.0 * PI();
	SET n = (c_1 + c_2) * 0.5;
	SET n = n * n * n * n * n * n * n;
	-- The hue rotation correction term is designed to account for the
	-- non-linear behavior of hue differences in the blue region.
	SET r_t = -2.0 * SQRT(n / (n + 6103515625.0)) * SIN(PI() / 3.0 * EXP(p * p / (-25.0 * PI() * PI())));
	SET n = (l_1 + l_2) * 0.5;
	SET n = (n - 50.0) * (n - 50.0);
	-- Lightness.
	SET l = (l_2 - l_1) / (k_l * (1.0 + 0.015 * n / SQRT(20.0 + n)));
	-- These coefficients adjust the impact of different harmonic
	-- components on the hue difference calculation.
	SET t = 1.0	+ 0.24 * SIN(2.0 * h_m + PI() * 0.5)
			+ 0.32 * SIN(3.0 * h_m + 8.0 * PI() / 15.0)
			- 0.17 * SIN(h_m + PI() / 3.0)
			- 0.20 * SIN(4.0 * h_m + 3.0 * PI() / 20.0);
	SET n = c_1 + c_2;
	-- Hue.
	SET h = 2.0 * SQRT(c_1 * c_2) * SIN(h_d) / (k_h * (1.0 + 0.0075 * n * t));
	-- Chroma.
	SET c = (c_2 - c_1) / (k_c * (1.0 + 0.0225 * n));
	-- Returning the square root ensures that dE00 accurately reflects the
	-- geometric distance in color space, which can range from 0 to around 185.
	RETURN SQRT(l * l + h * h + c * c + c * h * r_t);
END //

DELIMITER ;

-- GitHub Project : https://github.com/michel-leonard/ciede2000-color-matching
--   Online Tests : https://michel-leonard.github.io/ciede2000-color-matching

-- L1 = 52.3   a1 = 21.9   b1 = 2.7
-- L2 = 53.8   a2 = 28.0   b2 = -3.1
-- CIE ΔE00 = 5.0119430211 (Bruce Lindbloom, Netflix’s VMAF, ...)
-- CIE ΔE00 = 5.0119254601 (Gaurav Sharma, OpenJDK, ...)
-- Deviation between implementations ≈ 1.8e-5

-- See the source code comments for easy switching between these two widely used ΔE*00 implementation variants.

Source code accuracy and reliability

The difference between Sharma and Lindbloom formulations never exceeds ±0.0003 on the final ΔE2000, which corresponds to the usual difference measured between two 32-bit implementations and is imperceptible to the human eye. Our 64-bit implementations, all consistent with each other, guarantee at least 10 correct decimal places, so choosing one formulation over another mainly depends on the desired interoperability. The formulation that appears by default on this page is the most commonly used (its micro-advantage lies in its community anchoring and its greater lightness than its analog when vectorized).

✎ If you find a comment in the source code that does not correspond to another language, please inform the author of the site, who will study your suggestion and incorporate it into the source code.

How do you convert RGB colors to L*a*b*?

Go to the AWK, C, Dart, Java, JavaScript, Kotlin, Lua, PHP, Python, Ruby or Rust page where such a converter (using D65 illuminant) is already implemented in addition to the color comparison function.

CIELAB value ranges and interpretation of the ΔE2000

In the CIELAB color space, the L* component represents lightness and typically ranges from 0 (black) to 100 (white). The a* and b* components represent color axes: a* goes from green to red, while b* goes from blue to yellow. In practice, a* and b* values usually fall between -128 and +127, although they can slightly exceed these limits depending on the color conversion.

k_l, k_c and k_h Parameters

The parameters k_l, k_c, and k_h are weighting factors applied to the lightness (ΔL*), chroma (ΔC*), and hue (ΔH*) terms in the CIEDE2000 formula. Their default value is 1, which corresponds to the standard viewing conditions recommended by the International Commission on Illumination. In practice, these coefficients are adjusted to reflect specific conditions: for example, k_l = 2 is sometimes used to give more weight to lightness differences (common in printing), while k_c or k_h may be reduced to increase tolerance to saturation or hue variations depending on quality control requirements. Depending on the context, these coefficients typically range between 0.5 and 2.

ΔE2000 (CIEDE2000) measures the perceived difference between two colors: 0 means the colors are identical, and higher values (up to around 185 in extreme cases) indicate a larger difference. For example, a ΔE2000 value around 5 means the colors are close, while a value around 15 means they are clearly different.

Example of use in SQL

-- Example usage of the ΔE*00 function in SQL

-- Color 1: l1 = 94.1   a1 = 30.7   b1 = 2.9
-- Color 2: l2 = 92.2   a2 = 26.4   b2 = -2.2

SELECT ciede_2000(l1, a1, b1, l2, a2, b2) AS delta_e;

-- .................................................. This shows a ΔE2000 of 3.8819773139
-- As explained in the comments, compliance with Gaurav Sharma would display 3.8819904826

Test results

The driver, written in the C99 language and featuring 250 precise static tests, has proved that this SQL function is interoperable with the CIEDE2000 function available in other programming languages.

CIEDE2000 Verification Summary :
  First Verified Line : 57.7,61,95.1,50.49,-53,-91.7,72.48542739412765
             Duration : 44.06 s
            Successes : 10000000
               Errors : 0
      Average Delta E : 62.9405
    Average Deviation : 4.2545206732635951e-15
    Maximum Deviation : 1.1368683772161603e-13

Files to download

Feel free to use these files provided by Michel, even for commercial purposes.

Community

If you’d like to leave your opinion on this SQL source code or CIEDE2000 in general, the guestbook already contains 1 messages in English, and 9 messages in total, so let us know what you think.