MD5 - 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 2005 Forums

Transact-SQL (2005)

MD5

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-10 : 13:28:59

How can i to update Field3 with hash value (Field1 +Field2)

declare @tbl1 table
(ID INT,
Field1 varchar(20),
Field2 varchar(10),
Field3 varchar(50)
)

INSERT INTO @tbl1
SELECT 1, 'Abc','A100',null

INSERT INTO @tbl1
SELECT 2,'Xyz', 'B200',null

INSERT INTO @tbl1
SELECT 3,'Mnop', 'A100',null

INSERT INTO @tbl1
SELECT 4, 'TT','C300',null

INSERT INTO @tbl1
SELECT 5,'AAAA', 'B200',null

select * from @tbl1

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-10 : 13:50:46

update @tbl1
set field3 = field1 + field2

Or am I missing the glaringly obvious reason why this won't work?

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 13:57:38

may be presence of nulls

update @tbl1
set field3 = coalesce(field1,'') + coalesce(field2,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-10 : 14:01:41

I have to update with Hash like below but with multiple fields.

DECLARE @FieldValue nvarchar(50);
SELECT @FieldValue = 'SQLData';
SELECT HashBytes('MD5', @FieldValue);

Output:
0x8F29C4E790B18CB5A33CEBCF65FAE37F

http://msdn.microsoft.com/en-us/library/ms174415(SQL.90).aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-10 : 15:35:20

You need to CAST all the columns you want to use HashBytes on to string and concatenate them:

SELECT HashBytes('MD5', Col1 + Col2 + ...)
FROM TableName

Alternativly, you could use a CHECKSUM or even hash the CHECKSUM.. :)

SELECT HashBytes('MD5', CAST(CHECKSUM(col1, col2) AS VARCHAR(50)))
FROM Table

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-10 : 15:58:34

Lamprey, it seems like good, but how do i update for all records.

I mean without using cursor.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-10 : 16:17:57

UPDATE YourTable
SET YourColumn = HashBytes('MD5', Col1 + Col2 + ...)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-10 : 16:48:06

tkizer,
i got different values

SELECT HashBytes('MD5', CAST(CHECKSUM(col1, col2) AS VARCHAR(50)))
FROM Table

and

UPDATE YourTable
SET YourColumn = HashBytes('MD5', Col1 + Col2 + ...)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-10 : 16:54:09

Well yeah, those are two different methods.

One Hashes the Checksum. The other just Hashes a big string of all the columns. Pick one way and use that way..

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-10 : 17:47:20

I got different before update and after update.

SELECT HashBytes('MD5', CAST(CHECKSUM(col1, col2) AS VARCHAR(50)))
FROM TableName

Update TableName
SET ColumnName= HashBytes('MD5', CAST(CHECKSUM(col1, col2) AS VARCHAR(50)))
FROM TableName

SELECT * From TableName

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-10 : 18:02:11

waht data type are you using? HashBytes returns a VARBINARY with a length up to 8000. Here is a sample that shows they should work:[code]
DECLARE @Foo TABLE (ID INT IDENTITY(1,1), Val VARCHAR(50), Val2 VARCHAR(50), HashCol VARBINARY(8000) )

INSERT @Foo
SELECT CAST(NEWID() AS VARCHAR(50)), CAST(NEWID() AS VARCHAR(50)), NULL
UNION ALL SELECT CAST(NEWID() AS VARCHAR(50)), CAST(NEWID() AS VARCHAR(50)), NULL
UNION ALL SELECT CAST(NEWID() AS VARCHAR(50)), CAST(NEWID() AS VARCHAR(50)), NULL
UNION ALL SELECT CAST(NEWID() AS VARCHAR(50)), CAST(NEWID() AS VARCHAR(50)), NULL
UNION ALL SELECT CAST(NEWID() AS VARCHAR(50)), CAST(NEWID() AS VARCHAR(50)), NULL
UNION ALL SELECT CAST(NEWID() AS VARCHAR(50)), CAST(NEWID() AS VARCHAR(50)), NULL

SELECT *, HashBytes('MD5', CAST(CHECKSUM(Val, Val2) AS VARCHAR(50)))
FROM @Foo

UPDATE @Foo
SET HashCol = HashBytes('MD5', CAST(CHECKSUM(Val, Val2) AS VARCHAR(50)))

SELECT *
FROM @Foo[code]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-10 : 18:13:57

may be i have different field type,

SELECT HashBytes('MD5', CAST(CHECKSUM([Field1],
CAST([Field2] as nvarchar(50)),
CAST([Field3] as nvarchar(50)),
CAST([Field4] as nvarchar(50)),
CAST([Field] as nvarchar(50))
)AS VARCHAR(255)))
FROM TableName

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-11 : 11:13:41

Ok, So given you original table I think the "issue" you are seeing what I mentioned before. HashBytes generates a VARBIANRY result. When you run a query like:

SELECT Field1, Field2, HashBytes('MD5', Field1 + Field2)
FROM @tbl1

You get a new column that is of type VARBINARY. When you update your VARCHAR column with that value there is an implicit converstion from VARBINARY to VARCHAR, thus why result you get after updating doesn't appear the same. And due to truncation, may not be the same. Here is another example based on your original post:

declare @tbl1 table 
(ID INT,
Field1 varchar(20),
Field2 varchar(10),
Field3 varchar(50),
Field4 VARBINARY(8000)
)

INSERT INTO @tbl1
SELECT 1, 'Abc','A100',null, null

INSERT INTO @tbl1
SELECT 2,'Xyz', 'B200',null, null

INSERT INTO @tbl1
SELECT 3,'Mnop', 'A100',null, null

INSERT INTO @tbl1
SELECT 4, 'TT','C300',null, null

INSERT INTO @tbl1
SELECT 5,'AAAA', 'B200',null, null



SELECT Field1, Field2, HashBytes('MD5', Field1 + Field2)
FROM @tbl1

UPDATE @tbl1
SET Field3 = HashBytes('MD5', Field1 + Field2),
Field4 = HashBytes('MD5', Field1 + Field2)

SELECT *
FROM @tbl1

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-11 : 11:29:53

Lamprey,
I have not same data type for "Field1" and "Field2", "Field1" is nvarchar and "Field2" is ntext, if i do this :SELECT Field1, Field2, HashBytes('MD5', Field1 + Field2)
FROM @tbl1

i get the following error :
Msg 8116, Level 16, State 4, Line 1
Argument data type ntext is invalid for argument 2 of checksum function.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-11 : 11:40:09

Well according to BOL, that type is not compatable with the CHECKSUM function:

quote:
Noncomparable data types are text, ntext, image, XML, and cursor, and also sql_variant with any one of the preceding types as its base type.

There are several options, the easiest is probably to just CAST the NTEXT as NVARCAHR(MAX):

declare @tbl1 table 
(ID INT,
Field1 varchar(20),
Field2 ntext,
Field3 varchar(50),
Field4 VARBINARY(8000)
)

INSERT INTO @tbl1
SELECT 1, 'Abc','A100',null, null

INSERT INTO @tbl1
SELECT 2,'Xyz', 'B200',null, null

INSERT INTO @tbl1
SELECT 3,'Mnop', 'A100',null, null

INSERT INTO @tbl1
SELECT 4, 'TT','C300',null, null

INSERT INTO @tbl1
SELECT 5,'AAAA', 'B200',null, null



SELECT Field1, Field2, HashBytes('MD5', Field1 + CAST(Field2 AS NVARCHAR(MAX)))
FROM @tbl1

UPDATE @tbl1
SET Field3 = HashBytes('MD5', Field1 + CAST(Field2 AS NVARCHAR(MAX))),
Field4 = HashBytes('MD5', Field1 + CAST(Field2 AS NVARCHAR(MAX)))

SELECT *
FROM @tbl1

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-11 : 13:42:55

Lamprey,
I can not change field type nvarchar to varbinary for updating field like Field4 VARBINARY(8000), because this field is using third party software, if i change that progra could not read the data. How can i update with out change field type nvarchar to nvarbinary? Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-11 : 15:33:27

Ok, store it as a varchar then.

You should probably read up on CASTing and CONVERTing datatypes. It seems like you are hung up on the DISPLAY representation of the data rather than the actual VALUE of the data. Additionally, whenever you are CASTing datatypes there is the potential issue for data truncation/loss. So, also be aware of that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources