0

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

thanks

2
Contributors
11
Replies
13
Views
6 Years
Discussion Span
Last Post by lttleastig
0

Replace ColumnName and TableName with your column and table name:

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

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

0

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
0

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

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

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

0

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.

Edited by buddylee17: n/a

0

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

0

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

0

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?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.