我需要将存储在数据库中的字符串转换为数值.结果可以是Integer(首选)或Bigint.此转换将在PL/pgSQL函数的数据库端完成.
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.
有人可以给我指出一些算法或任何可用于实现此目的的API吗?
Can someone please point me to some algorithm or any API's that can be used to achieve this?
我已经在Google上搜索了几个小时,到目前为止,找不到有用的东西:(
I have been searching for this on Google for hours now, could not find anything useful so far :(
推荐答案只需保留MD5哈希的前32位或64位.当然,它使md5的主要属性无效(=碰撞几率是无穷小),但是您仍然会分散大量的值,这大概足以解决您的问题.
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函数:
SQL functions derived from the other answers:
对于bigint:
create function h_bigint(text) returns bigint as $$ select ('x'||substr(md5($1),1,16))::bit(64)::bigint; $$ language sql;对于int:
create function h_int(text) returns int as $$ select ('x'||substr(md5($1),1,8))::bit(32)::int; $$ language sql;更多推荐
在PostgreSQL中将字符串散列为数值
发布评论