Hashing a String to a Numeric Value in PostgreSQL

Question

I need to Convert Strings stored in my Database to a Numeric value. Result can be Integer (preferred) or Bigint. This conversion is to be done at Database side in a PL/pgSQL function.

Can someone please point me to some algorithm or any API's that can be used to achieve this?

I have been searching for this on Google for hours now, could not find anything useful so far :(

Answer 1

Just keep the first 32 bits or 64 bits of the MD5 hash. Of course, it voids the main property of md5 (=the probability of collision being infinitesimal) but you'll still get a wide dispersion of values which presumably is good enough for your problem.

SQL functions derived from the other answers:

For bigint:

create function h_bigint(text) returns bigint as $$  select ('x'||substr(md5($1),1,16))::bit(64)::bigint; $$ language sql; 

For int:

create function h_int(text) returns int as $$  select ('x'||substr(md5($1),1,8))::bit(32)::int; $$ language sql; 

Answer 2

You can create a md5 hash value without problems:

select md5('hello, world'); 

This returns a string with a hex number.

Unfortunately there is no built-in function to convert hex to integer but as you are doing that in PL/pgSQL anyway, this might help:

https://stackoverflow.com/a/8316731/330315