return numeric - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

return numeric

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-06-26 : 09:13:37

i am looking for a function that return numeric value?

for example:
i send to function "hello world" and i want to get numeric value like 432452

(i know that is exist in ORACLE that called "HASH")

do we have similar in MSSQL?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-26 : 09:17:23

You may be looking for CHECKSUM function: http://msdn.microsoft.com/en-us/library/ms189788.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-26 : 10:11:09

or maybe HASHBYTES ?

KH
[spoiler]Time is always against us[/spoiler]

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-06-28 : 09:15:42

hash is good but it is not retun numeric

i wrote
SELECT HashBytes('SHA1', 'helloworld')

output:
0x6ADFB183A4A2C94A2F92DAB5ADE762A47889A5A1

i want to get number like 43224

(i know that in oracle i have hash function that retun number)

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-28 : 16:41:26

What you got is number, very big one.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-06-29 : 01:39:50

ok,

can i get a small number?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-29 : 01:56:19

convert it to smallint ?


select	convert(smallint, hashbytes('SHA1', 'hello world'))

Actually what are you trying to achieve here ? What is the use of the numeric value ?

KH
[spoiler]Time is always against us[/spoiler]

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-06-29 : 09:25:37

SELECT CONVERT(smallint,0x666102C87CAA35FFD41B757F81EF7D1ABBC2A0B9)
-24391
SELECT CONVERT(smallint,0x6DA079686DB446D9F57A87BBDDD2F8AF475CA0B9)
-24391

i get same number.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-29 : 09:41:47

You have conflicting requirements here - if the number you want to get is small, it has only fewer combinations, so by necessity some of them will repeat.

The only thing you can be guaranteed of is that the same string will produce the same number. But the reverse - that different strings won't return the same number cannot be guaranteed.

When you cast to smallint, you are using only the right-most two bytes. You can reduce the probably by casting to bigint - you will be using 8 bytes. You can see this in the examples below.

SELECT CONVERT(smallint,0x666102C87CAA35FFD41B757F81EF7D1ABBC2A0B9)
SELECT CONVERT(smallint,0x6DA079686DB446D9F57A87BBDDD2F8AF475CA0B9)

-- same as the above two (because the last two bytes are the same for all three)
SELECT CONVERT(smallint,0xA0B9)

-- Convert to bigint and you will use 8 bytes, so you will get different numbers
SELECT CONVERT(bigint,0x666102C87CAA35FFD41B757F81EF7D1ABBC2A0B9)
SELECT CONVERT(bigint,0x6DA079686DB446D9F57A87BBDDD2F8AF475CA0B9)

-- but you will get same number if the last 8 bytes are the same.
-- This pair returns the same numbers as the pair above.
SELECT CONVERT(bigint,0xF81EF7D1ABBC2A0B9)
SELECT CONVERT(bigint,0xBDDD2F8AF475CA0B9)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources