WEIGHTED_CHAR_CHECKSUM

Compute a position-weighted character checksum of a string.

Category: hashReturns: BIGINTDialect: Standard

Syntax

WEIGHTED_CHAR_CHECKSUM(str)

Description

## Overview Computes a simple position-weighted checksum of the input string. Each character's Unicode code point is multiplied by its 1-based character position and the products are summed. Because position is factored into the computation, anagrams (same characters in different order) produce different checksums, which distinguishes this function from purely additive checksums. The function is non-cryptographic and has a small output space relative to large hash functions. Use it for lightweight position-sensitive change detection on short strings, not for partitioning at scale or any security purpose. ## Behavior - Returns NULL for NULL input. - Returns 0 for the empty string. - Deterministic across calls. - Position is 1-based; the first character's weight is 1, not 0. - Unicode: the code point is used, not the UTF-8 byte form. - Output is an integer; exact type depends on the engine (typically BIGINT to avoid overflow). ## Algorithm - sum(position * code_point) for all characters, 1-based. - No RFC; this is an engine-defined lightweight checksum. ## Compatibility - Not a standard algorithm. Semantics are engine-specific; do not assume portability of values across implementations.

Parameters

Name	Type	Description
`str`		Specifies the string whose weighted checksum is computed. Each character's Unicode code point is multiplied by its 1-based position and summed. Returns NULL for NULL input.

Examples

-- Basic checksum
SELECT WEIGHTED_CHAR_CHECKSUM('hello') AS c;

-- Anagrams produce different values because position matters
SELECT WEIGHTED_CHAR_CHECKSUM('abc') AS c1,
       WEIGHTED_CHAR_CHECKSUM('cba') AS c2;

-- Empty string yields 0
SELECT WEIGHTED_CHAR_CHECKSUM('') AS c;

-- Position sensitivity on 2-character strings
SELECT WEIGHTED_CHAR_CHECKSUM('ab') AS c1,
       WEIGHTED_CHAR_CHECKSUM('ba') AS c2;

-- Realistic: quick position-sensitive fingerprint per code column
SELECT product_id, WEIGHTED_CHAR_CHECKSUM(product_code) AS code_fp
FROM ecommerce.catalog.products;

Pitfalls

Small output space relative to real hash functions. Collisions are common; do not use for deduplication at scale.
Not cryptographically secure. Do not use for signatures or tamper detection.
Weight is 1-based; a leading space at position 1 has weight 1, not 0. Subtle input cleaning changes the checksum.
Unicode code point is used, not byte length. Surrogate-pair handling varies by engine for characters above U+FFFF.
Not a standard algorithm; values are not portable across SQL engines. Do not persist expecting cross-system stability.