CODEWITHSUNDEEP
sqlsql-server
w0051977
w0051977

Reputation: 15807

Cast varbinary to varchar

Please see the SQL below:

select substring(hash1,1,4) from (
    select hashbytes('SHA1', 'Ian' + 'Maria') as hash1
    ) as hashtable

which returns:

0xC0326BA0

There are eight characters returned: i.e. 0X + 8 characters. Why is there 8 characters returned? I realise this is a simple question, but I have not managed to find an answer tonight.

Upvotes: 0

Views: 458

Answers (1)

Morgan Thrapp
Morgan Thrapp

Reputation: 9986

When you use SUBSTRING on a binary data type, it returns length bytes, not characters, so in this case, you're getting back the four bytes C0, 32, 6B, and A0.

If you really need exactly 4 characters of random binary data interpreted as a string, you could do this:

select substring(CAST(hash1 as varchar(4)),1,4) from (
    select hashbytes('SHA1', 'Ian' + 'Maria') as hash1
    ) as hashtable

But it won't necessarily be data that means anything.

Upvotes: 4

Related Questions