INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to hashbytes SQL information for one record spanning multiple rows

How to hashbytes SQL information for one record spanning multiple rows

How to hashbytes SQL information for one record spanning multiple rows

(OP)
Hello. Running SQL 2008.

I have the following sample data:

create table #temp (irecordid int, name varchar(100))
insert into #temp values (2373691, 'HURT, CYNTHIA')
insert into #temp values (2373691 , 'HURT, STEVEN')
insert into #temp values (2378322 , 'SUNSET CUSTOM HOMES, ')
insert into #temp values (2378322 , 'TORRES, NOE')
insert into #temp values (2378322 , 'NOE TORRES INC, ')
insert into #temp values (2373827, 'MILLER, BEVERLY')
insert into #temp values (2373827, 'MILLER, RONNIE')


I'm hoping to get a single result of the irecordid and a hash of all of the 'name' entries for each irecordid. For example:
Irecordid HASH
2373691 0x42DDD2300DAB2A93AC7DDF64FF479ECD
2378322 0x0D517BF2056B62FFA116A27B53D3B31D
2373827 0xDB2D7575E3A72E028DF0F8DF8EFF8933


I can get the results one at a time with the following query:

CODE

select distinct irecordid, HASHBYTES('md5', CONVERT(varbinary(max), (select name from #temp where irecordid = 2373827 for xml auto )))
from #temp
where irecordid = 2373827 

However, I'd like to get this with one script for my entire table. I have tried with grouping, but just can't get my syntax correct.

Any suggestions?

Thanks!

Brian

RE: How to hashbytes SQL information for one record spanning multiple rows

Why that complicated?

Untested, but this should work as simple as

CODE

SELECT irecordid, HASHBYTES('md5', name) as HASH FROM #temp 

https://msdn.microsoft.com/en-us/library/ms174415(...

HashBytes ( '<algorithm>', { @input | 'input' } )

This syntax means you can use a variable or string. Where a string is allowed, you can also allow a single char/varchar/nchar/nvarchar column. No need for a FOR XML query.

And if you would need multiple columns, there is a recommended solution in the community additions to the help topic:

CODE

SELECT name, 
  [HASHKEY] = (  HASHBYTES('MD5', ( select name, database_id, create_date , source_database_id FOR XML RAW)))  
FROM SYS.DATABASES 

This would translate to this in your case:

CODE

SELECT irecordid, 
  [HASH] = (  HASHBYTES('MD5', ( select name FOR XML RAW)))  
FROM #temp 

Since your hash only is from one field, this is unnecessary. Also, multiple string fields could simply be concatenated to one value, eg HASHBYTES('MD5',Firstname+Lastname) would work, when a table has firstname and lastname columns.

Bye, Olaf.

RE: How to hashbytes SQL information for one record spanning multiple rows

(OP)
Thanks for the reply Olaf.

Hashing a single row is simple. Especially using your example:

CODE

SELECT irecordid, HASHBYTES('md5', name) as HASH FROM #temp 
Which gives me the results:
2373691 0x6AC684AC73C1E97E8687A4C36EBDF278
2373691 0xE11DDD7308A696277FBFD6B43CC426DD
2378322 0x3427FDEBE8CC340AC607FC4DC05C5180
2378322 0xFF6F955006259F8C7D404C6290247B35
2378322 0x972BBEE62828DD55B15F888899039682
2373827 0x14E9E578BA41A6C4A60BCDB22105856A
2373827 0x31A98A46C37C90485F4382D135B5A44C


The problem I'm having is that I'd like to hash all rows for EACH irecordid grouped together. Instead of receiving results like those above (one hash for each row). I'd like results like results shown below (one hash for each irecordid):

2373691 0x42DDD2300DAB2A93AC7DDF64FF479ECD
2378322 0x0D517BF2056B62FFA116A27B53D3B31D
2373827 0xDB2D7575E3A72E028DF0F8DF8EFF8933

Any idea how I can group this?

Thanks!
Brian

RE: How to hashbytes SQL information for one record spanning multiple rows

How about...

CODE

SELECT irecordid,
       (SELECT '' + name FROM #temp WHERE irecordid = t.irecordid ORDER BY name ASC FOR XML PATH('')) ConcatNames,
       HASHBYTES('md5', (SELECT '' + name FROM #temp WHERE irecordid = t.irecordid ORDER BY name ASC FOR XML PATH(''))) HashNames
  FROM #temp t
 GROUP BY irecordid 

RE: How to hashbytes SQL information for one record spanning multiple rows

(OP)
Yes!! Thanks DaveInIowa. That's exactly what I needed.

RE: How to hashbytes SQL information for one record spanning multiple rows

Sorry, for not looking at the data in detail. The first impression I had is #temp is already grouped by irecordid. Why else a temp table? If you want to prepare some sample data for testing in a posting, I'd prefer and suggest using a table variable.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close