Skip to main content

ora_hash

1. ORA_HASH Function​

The ORA_HASH function in H2 database is used to calculate a hash value for a given expression or column. It returns a 32-bit hash value.

2. Syntax​

The syntax for the ORA_HASH function in H2 database is as follows:

ORA_HASH(expr [,max_buckets])

Arguments​

  • expr: The expression or column for which the hash value needs to be calculated. It can be any valid expression or column name.
  • max_buckets (optional): The maximum number of buckets in which the hash value can be distributed. It should be an integer value.

Return​

  • The ORA_HASH function returns a 32-bit hash value for the given expression or column.

3. Notes​

  • The ORA_HASH function in H2 database is not an exact replica of the Oracle ORA_HASH function. It is a similar implementation specific to H2 database.
  • The hash value generated by the ORA_HASH function can be used for data partitioning, data distribution, or hash-based algorithms.
  • The max_buckets parameter is optional and provides control over the distribution of hash values. If not specified, it defaults to the number of available buckets in H2 database.

4. Examples​

Here are a few examples demonstrating the usage of the ORA_HASH function in H2 database:

Example 1 - Calculating the hash value for a string expression:​

SELECT ORA_HASH('Hello World') AS hash_value;

Output:

HASH_VALUE
----------
-1970682507

Example 2 - Calculating the hash value for a column:​

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50)
);

INSERT INTO users VALUES (1, 'john.doe'), (2, 'jane.smith'), (3, 'bob.johnson');

SELECT id, username, ORA_HASH(username) AS hash_value
FROM users;

Output:

ID | USERNAME   | HASH_VALUE
---+------------+--------------
1  | john.doe   | -1265555863
2  | jane.smith |  1984234915
3  | bob.johnson|  375462663
  • MD5 - Calculate MD5 hash value
  • SHA1 - Calculate SHA-1 hash value