none
Hashbytes calculations in MD5

    질문

  • Hi

    When I run  SELECT CASE WHEN FORMAT(95630.0, '0.####') = '95630' THEN 1 ELSE 0 END

    I get the result as 1

    But I when I calculate  HASHBYTES for the following values I get different Hash values. Why ?

     SELECT CONVERT(NVARCHAR(32),HASHBYTES('MD5',format(95630.0, '0.####')),2) SQHVAL

    Result:3C66F33DF275632E038D5395D46BEB5F

     SELECT CONVERT(NVARCHAR(32),HASHBYTES('MD5','95630'),2) SQHVAL

    Result: FF5E0DB92A7DBF4D8B02C888188D76C3

    Basically, I am trying compare Oracle data to SQL server data. conversion Of Number(16,4) to Numeric(16,4) to Hashbytes are getting different values. Can any one tell me conversion mask to be applied?

    2018년 7월 13일 금요일 오전 5:40

모든 응답

  • The reason why you're getting different hash values is because FORMAT expression itself returns the data in unicode format.

    In the second  case you're passing the value as non unicode data hence the difference

    Try like this and it should give same value in both cases

    SELECT CONVERT(NVARCHAR(32),HASHBYTES('MD5',format(95630.0, '0.####')),2) SQHVAL1, CONVERT(NVARCHAR(32),HASHBYTES('MD5',CONVERT(NVARCHAR(32),'95630')),2) SQHVAL2
    
    
    
    /*
    Output
    -----------------------------------------------------------
    SQHVAL1	SQHVAL2
    ------------------------------------------
    3C66F33DF275632E038D5395D46BEB5F	3C66F33DF275632E038D5395D46BEB5F
    
    
    */

    Similarly the other way around i.e converting unicode to non unicode

    SELECT CONVERT(NVARCHAR(32),HASHBYTES('MD5',CAST(format(95630.0, '0.####') as varchar(10))),2) SQHVAL1,CONVERT(NVARCHAR(32),HASHBYTES('MD5','95630'),2) SQHVAL2
    
    
    /*
    Output
    -----------------------------------------------------
    SQHVAL1	SQHVAL2
    ----------------------------------------------------
    FF5E0DB92A7DBF4D8B02C888188D76C3	FF5E0DB92A7DBF4D8B02C888188D76C3
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    2018년 7월 13일 금요일 오전 6:09
  • I think I found the reason. It appers it needs conversion for the indivual column lso.

    SELECT CONVERT(NVARCHAR(32),HASHBYTES('MD5',Convert(VARCHAR,format(95630.0, '0.####'))),2) SQHVAL

    Result:FF5E0DB92A7DBF4D8B02C888188D76C3

     SELECT CONVERT(NVARCHAR(32),HASHBYTES('MD5','95630'),2) SQHVAL

    Result:FF5E0DB92A7DBF4D8B02C888188D76C3

    Now I need to find the same for Date and Time stamp conversions

    2018년 7월 13일 금요일 오후 12:52
  • I think I found the reason. It appers it needs conversion for the indivual column lso.

    SELECT CONVERT(NVARCHAR(32),HASHBYTES('MD5',Convert(VARCHAR,format(95630.0, '0.####'))),2) SQHVAL

    Result:FF5E0DB92A7DBF4D8B02C888188D76C3

     SELECT CONVERT(NVARCHAR(32),HASHBYTES('MD5','95630'),2) SQHVAL

    Result:FF5E0DB92A7DBF4D8B02C888188D76C3

    Now I need to find the same for Date and Time stamp conversions

    Thats the same thing I suggested in my last post :)

    I think you didnt read it


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 7월 13일 금요일 오후 1:38