String to Value Algorithm

Well continuing on the thought of conversion routines, here is an example of taking a string value in Transact-SQL on Microsoft SQL Server (may work on other platforms that support same functions) and creating a unique integer value.

Code listing:

DECLARE @text nvarchar(100), @value bigint
SET @text = 'Smith'
SET @value = 0
WHILE LEN(@text) > 0
BEGIN
SET @value = @value + ASCII(LEFT(@text, 1)) * square(LEN(@text))
SET @text = RIGHT(@text, LEN(@text) - 1)
END
SELECT @value

Very simple hopefully. The basis is using the length of the string, create a large integer value that is multiplied against the ascii value of each letter in the string. I was asked this as a question, so I have not explored all of the uses of a function like this; however, as a quick hash it seems to work fine.

Again, just posting for the learning of some of the tools available in SQL like the ASCII and SQUARE functions. Hopefully making the life of some other DBA or programmer a little easier.


References:

• BigInt: http://msdn.microsoft.com/en-us/library/ms187745(SQL.90).aspx
• Numeric: http://msdn.microsoft.com/en-us/library/ms187746(SQL.90).aspx
• ASCII: http://msdn.microsoft.com/en-us/library/ms177545.aspx
• Square: http://msdn.microsoft.com/en-us/library/ms173569.aspx