Thread: [Q] string to int hash function for small range

I would like to have a function
given a user id varchar(64) to get a hash number between 0 and 255.

I first tried md5 but the number it returns is very big and cannot
be converted to an INT

there is likely a way to simply add ascii values of the userId together
to get a small integer, but wanted to know if there are any other
'built-in' ways

this is to get the database id based on user id.

thanks in advance,
Vlad
--
  V S P
  toreason@fastmail.fm

--
http://www.fastmail.fm - One of many happy users:
  http://www.fastmail.fm/docs/quotes.html

V S P wrote:
> I would like to have a function
> given a user id varchar(64) to get a hash number between 0 and 255.
>
> I first tried md5 but the number it returns is very big and cannot
> be converted to an INT
>
> there is likely a way to simply add ascii values of the userId together
> to get a small integer, but wanted to know if there are any other
> 'built-in' ways
>
> this is to get the database id based on user id.
>

maybe take the last byte (2 chars) of the md5, and convert to integer?
something like....


    get_byte(decode(md5(id),'hex'),16)

On Sat, Mar 07, 2009 at 09:33:33PM -0500, V S P wrote:
> I would like to have a function
> given a user id varchar(64) to get a hash number between 0 and 255.
>
> I first tried md5 but the number it returns is very big and cannot
> be converted to an INT
>
> there is likely a way to simply add ascii values of the userId together
> to get a small integer, but wanted to know if there are any other
> 'built-in' ways
>
> this is to get the database id based on user id.

this is bad idea, as there will be collisions. better use sequential
ids.

but if you *have to*:

select hashtext('username') % 128 + 127;

depesz;

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

or do a mod 256 e.g.

mod(get_byte(md5(id),'hex'),256)

HTH
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.




> Date: Sat, 7 Mar 2009 19:23:25 -0800
> From: pierce@hogranch.com
> To: toreason@fastmail.fm
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] [Q] string to int hash function for small range
>
> V S P wrote:
> > I would like to have a function
> > given a user id varchar(64) to get a hash number between 0 and 255.
> >
> > I first tried md5 but the number it returns is very big and cannot
> > be converted to an INT
> >
> > there is likely a way to simply add ascii values of the userId together
> > to get a small integer, but wanted to know if there are any other
> > 'built-in' ways
> >
> > this is to get the database id based on user id.
> >
>
> maybe take the last byte (2 chars) of the md5, and convert to integer?
> something like....
>
>
> get_byte(decode(md5(id),'hex'),16)
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Express your personality in color! Preview and select themes for Hotmail®. See how.

On Sat, Mar 7, 2009 at 7:33 PM, V S P <toreason@fastmail.fm> wrote:
> I would like to have a function
> given a user id varchar(64) to get a hash number between 0 and 255.
>
> I first tried md5 but the number it returns is very big and cannot
> be converted to an INT
>
> there is likely a way to simply add ascii values of the userId together
> to get a small integer, but wanted to know if there are any other
> 'built-in' ways
>
> this is to get the database id based on user id.

In psql type this:

\df *hash*

Ok, thank you

using   \df *hash*  from psql prompt
I can see that is how I have to access this function

select pg_catalog.hashtext('myusername')

I will also play with other suggestions of get_byte
of the MD5 result casted to a string.

thanks again for all the replies,
Vlad





On Sat, 07 Mar 2009 21:17 -0700, "Scott Marlowe"
<scott.marlowe@gmail.com> wrote:
> On Sat, Mar 7, 2009 at 7:33 PM, V S P <toreason@fastmail.fm> wrote:
> > I would like to have a function
> > given a user id varchar(64) to get a hash number between 0 and 255.
> >
> > I first tried md5 but the number it returns is very big and cannot
> > be converted to an INT
> >
> > there is likely a way to simply add ascii values of the userId together
> > to get a small integer, but wanted to know if there are any other
> > 'built-in' ways
> >
> > this is to get the database id based on user id.
>
> In psql type this:
>
> \df *hash*
--
  V S P
  toreason@fastmail.fm

--
http://www.fastmail.fm - The professional email service