2

I'm trying to generate a hash value of a specific string in MD5 Base64. Is it possible to do it in a query within SQL Server?

I know there's a function to generate hashes in HashBytes(Algo,String), but there's no Base64 option.

Is there?

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DarenTay
  • 41
  • 1
  • 3
  • Does this answer your question? [Base64 encoding in SQL Server 2005 T-SQL](https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql) – Ian Kemp Sep 08 '21 at 10:43

1 Answers1

8
CREATE FUNCTION dbo.Base64Encode
(
    @bin VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN   
    return CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'VARCHAR(MAX)')
END

SELECT dbo.Base64Encode(HashBytes('MD5', ItemName))
FROM tblItems
Magnus
  • 45,362
  • 8
  • 80
  • 118
  • Hi Magnus, it works! thanks! 2 questions from this though, as I am not familiar with MSSQL: 1) I just need to run this create function query once, so that the function will exist within the MSSQL database. But how do I find the function from the MSSQL Management Studio? 2) From the same MSSQL Management Studio, all select statements I used have to be something like: select column from ...... how do I just specify the table.column.. or shorten the field? Thanks!!! :)
     – DarenTay Oct 22 '11 at 15:24
  • Yes you only need to create it once, you'll find it in Management Studio beneath `Programmability --> Functions --> Scalar-Values Functions`. If you batch begins with `Use DbName` you don't have to specify it inside the query. – Magnus Oct 22 '11 at 17:56
  • Hi Magnus, i notice the function appears in 'Master'. 1) How do I create a function that appears in the specific database I am working on? 2) Is there any added advantage/disadvantage if the function is in the master database or not? 3) Even with "Use DBname", I will still need to specifiy . under the 'from' part of the query. is that expected? Sorry I'm new to SO, and I must be really a blind mouse here... but I can't seem to find where can I accept the answer to acknowledge
     – DarenTay Oct 24 '11 at 00:36
  • 1. Use the `USE DbName` statement before the crate function statement. 2. Don't put it in the master. 3. You only need to specify Schema.Function not Schema.Table if the schema is dbo. – Magnus Oct 24 '11 at 14:12
  • i guess i need to play around with it more to figure it out thanks! – DarenTay Oct 25 '11 at 08:29
  • Hi Magnus, just to clarify, you're saying after using `USE DbName` I will need to query the db or function usng Schema.Function or Schema.table respectively. But is there anyway, within the query, I just specify the function name or table name, to simplify the queries? Thanks! – DarenTay Oct 27 '11 at 01:09