Find a solution

sql - A HashBytes konvertálása VarChar-ba

original title: "sql - Convert HashBytes to VarChar"

Translate

I want to get the MD5 Hash of a string value in SQL Server 2005. I do this with the following command:

SELECT HashBytes('MD5', 'HelloWorld')

However, this returns a VarBinary instead of a VarChar value. If I attempt to convert 0x68E109F0F40CA72A15E05CC22786F8E6 into a VarChar I get há ðô§*à\Â'†øæ instead of 68E109F0F40CA72A15E05CC22786F8E6.

Is there any SQL-based solution?

Yes

Szeretnék megszerezni az MD5 Hash karakterláncértékét az SQL Server 2005-ben. Ezt a következő paranccsal hajtom végre: SELECT HashBytes ('MD5', 'HelloWorld'). Ez azonban VarBinary-t ad vissza, nem VarCh ...

Ez az összefoglalás a fordítás után. Ha meg szeretné tekinteni a teljes fordítást, kattintson a "fordítás" ikonra

Minden válasz
  • GateKiller
    Translate

    I have found the solution else where:

    SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'HelloWorld')), 3, 32)
  • Rapscallion
    Translate 
    SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', 'Hello World'),2)
  • Xaqron
    Translate

    Use master.dbo.fn_varbintohexsubstring(0, HashBytes('SHA1', @input), 1, 0) instead of master.dbo.fn_varbintohexstr and then substringing the result.

    In fact fn_varbintohexstr calls fn_varbintohexsubstring internally. The first argument of fn_varbintohexsubstring tells it to add 0xF as the prefix or not. fn_varbintohexstr calls fn_varbintohexsubstring with 1 as the first argument internaly.

    Because you don't need 0xF, call fn_varbintohexsubstring directly.

  • Timo Riikonen
    Translate

    Contrary to what David Knight says, these two alternatives return the same response in MS SQL 2008:

    SELECT CONVERT(VARCHAR(32),HashBytes('MD5', 'Hello World'),2)
SELECT UPPER(master.dbo.fn_varbintohexsubstring(0, HashBytes('MD5', 'Hello World'), 1, 0))

    So it looks like the first one is a better choice, starting from version 2008.

  • Ramans
    Translate 
    convert(varchar(34), HASHBYTES('MD5','Hello World'),1)

    (1 for converting hexadecimal to string)

    convert this to lower and remove 0x from the start of the string by substring:

    substring(lower(convert(varchar(34), HASHBYTES('MD5','Hello World'),1)),3,32)

    exactly the same as what we get in C# after converting bytes to string

  • Simon Jones
    Translate

    With personal experience of using the following code within a Stored Procedure which Hashed a SP Variable I can confirm, although undocumented, this combination works 100% as per my example:

    @var=SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA2_512', @SPvar)), 3, 128)
  • anopres
    Translate

    Changing the datatype to varbinary seems to work the best for me.

Hasonló problémák

Legutóbbi kérdések