Friday, September 15, 2017

Creating Hash Values for Strings In MSSQL, DB2 UDB for LUW, and Oracle

It will often occur that we need to search a large character string in a relational database, something like an email address, postal address, etc.

For the most part, when we do such searches, we're searching for a sub-string within that large string of characters.

However, for instances where we know we need an equivalency search, we can make things more efficient by generating a hash of the string and then storing that in an indexed column.

This can be done in all 3 of the major RDBMS platforms.  Links below:

MSSQL uses the CHECKSUM() function.
DB2 LUW uses the DBMS_UTILITY.GET_HASH_VALUE() function.
Oracle uses the ORA_HASH function.

This article by Jeff Reinhard over at SQLServerCentral.com is what inspired me to look for the similar function in DB2 & Oracle, and it does a very nice job of explaining the use case for this using an e-mail address scenario.

If anyone knows how to do this in postgressql or MySQL, I'd love to have you add a few words or link in the comments. Thanks! :)

1 comment:

markthomson said...

I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. Bethlehem Web Developers

DBT-50000 when using DBCA.bat on Windows (Oracle 19.11)

I’ve been having some trouble getting DBCA to run in order to create databases. Thought I’d share it with you, and thus document it for la...