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

thanks

Recommended Answers

Replace ColumnName and TableName with your column and table name:

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

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
Jump to Post
INSERT INTO DestinationTable(DestinationColumn)
 SELECT SUBSTRING(master.sys.fn_varbintohexstr(HashBytes('MD5', SourceColumn)),3,19) FROM SourceTable
Jump to Post

All 11 Replies

Replace ColumnName and TableName with your column and table name:

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

thanks...
solved my prob

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...

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

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

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

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

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.

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

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

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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.