We are also trying to simulate String hashcode of Java in SQL. Why do I get this error when I try to do hashcode in SQL using the below code instead? I'm using this sample to run it:
Select dbo.fn_get_string_hdp_group_id('000b4c09-9b4b-42b0-88a4-7c348be6a92d');
Here is the error we get:
Arithmetic overflow error converting expression to data type numeric.
And this is the function we wrote in SQL. It looks to my eye as the equivalent of the Java hatched method:
ALTER FUNCTION fn_get_string_hdp_group_id(@text varchar(50))
returns int
BEGIN
Declare @result int, @index int, @char char(1), @calcVal decimal(38,0);
Set @calcVal = 0;
Set @result = 0;
Set @index = 1;
While(@index <= Len(@text))
BEGIN
Set @char = Substring(@text, @index, 1);
Set @calcVal = (@calcVal * 31) + (Ascii(@char));
END
Set @result = Abs(@calcVal) % 10;
return @result;
END
GO
Thanks much in advance!