MS SQL 2008 R2 - Convert column into md5

[lttleastig]

Hello
Is there a querry to convert a whole column into MD5 but i only need the first 19 characters of the MD5

thanks

[buddylee17]

Replace ColumnName and TableName with your column and table name:

SELECT SUBSTRING(HashBytes('MD5', ColumnName),1,19) [Hashed] FROM TableName

[lttleastig]

thanks...
solved my prob

[lttleastig]

umm sorry did not actually solve my prob
it outputs 0x4429BDEEB9E472EC1CAC4608024A7C7E
but i only want 4429BDEEB9E472EC1CA
dont need the 0x at the beginning and only need the first 19 chars
and also i want it to save the output directly into the column

thanks...

[buddylee17]

I agree. The data is varbinary and is being implicitly converted. Here's what you need:

SELECT SUBSTRING(master.sys.fn_varbintohexstr(HashBytes('MD5', ColumnName)),3,19) FROM TableName

[lttleastig]

ok now it outputs the correct values then how do i insert them back into the column?

[buddylee17]

INSERT INTO DestinationTable(DestinationColumn)
 SELECT SUBSTRING(master.sys.fn_varbintohexstr(HashBytes('MD5', SourceColumn)),3,19) FROM SourceTable

[lttleastig]

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'IX_UserInfoUserID'. Cannot insert duplicate key in object 'dbo.UserInfo'.
The statement has been terminated.

Btw the source and destination are the same column

[buddylee17]

The column you are inserting into has an index with a unique constraint. Either rethink your database design or remove the constraint. To remove the constraint, Under indexes, open IX_UserInfoUserID and uncheck the unique checkbox.

[lttleastig]

yeah it has an index because the DB is for a game and it connects the characters to the accounts by using them

Basically what i want to do is convert the whole column UserPass into MD5 but only the first 19 chars excluding 0x

[lttleastig]

is there no way to do it like this then?
there is a userID w/c is unique to individual aacounts
like insert into table column where userid=userid
i dont know that much about how it works...
what i did is i made a copy of the database so that the source and destination will be diff

[lttleastig]

One of my friends gave me this code

<?php

set_time_limit(10);

$dbuser = "sa";
$dbpass = "1234";
$dbname = "RanUser";
$dbhost = "..\SQLEXPRESS";
$table = "UserInfo";

$conn = mssql_connect($dbhost, $dbuser, $dbpass) or die("<center>connect failed</center>");
@mssql_select_db($dbname, $conn) or die("<center>DB don't exists or cant access to it</center>");

$result = mssql_query("SELECT UserName,UserPass,UserPass2 FROM $table");
$all = mssql_num_rows($result);

for($i=0;$i<$all;++$i) {
$rows = mssql_fetch_row($result);

$pass = strtoupper(substr(md5($rows[1]),0,19));
$pass2 = strtoupper(substr(md5($rows[2]),0,19));
$id = $rows[0];

if (!empty($rows)) {
mssql_query("UPDATE $table SET UserPass = '$pass', UserPass2 = '$pass2' where UserName = '$id'");
} else {
echo 'Update Failed!! Fix me again!';
}
}

?>

but when i use it it shows an error

Fatal error: Maximum execution time of 10 seconds exceeded in Z:\www\convert.php on line 26

only changes a certain ammount of the accounts into MD5 how can that be fixed?