I need help in decoding a Base64 password column in my database. When I copy a single column content into https://www.base64decode.org/ it decodes perfectly. I just need to perform this on ~7000 rows in this column. Any help is greatly appreciated.
- I found this code that works when I paste in any value from the column. Now I just need to know how to use this to take from the password column and put into a new table as a decoded result. Thank you.Pacoletaco– Pacoletaco2016-11-01 16:54:19 +00:00Commented Nov 1, 2016 at 16:54
- 1please explain with some data and explain expected result.your question is not clearTheGameiswar– TheGameiswar2016-11-01 18:13:17 +00:00Commented Nov 1, 2016 at 18:13
- Have you attempted to do anything to solve your issue?dfundako– dfundako2016-11-01 19:06:40 +00:00Commented Nov 1, 2016 at 19:06
- Are you trying to decode from SQL Server Management Studio, or through your own application, or what? More information would help you get an answer.dub stylee– dub stylee2016-11-01 19:09:03 +00:00Commented Nov 1, 2016 at 19:09
Add a comment |
2 Answers
You can use the following (source):
declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max) set @source = convert(varbinary(max), 'Hello Base64') set @encoded = cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)') set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)') select convert(varchar(max), @source) as source_varchar, @source as source_binary, @encoded as encoded, @decoded as decoded_binary, convert(varchar(max), @decoded) as decoded_varchar ... but, creating a function for this, then:
create function fnDecodeBase64 (@encoded as varchar(max)) returns varchar(max) as begin declare @decoded varchar(max) set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)') return @decoded end So, you can use:
select dbo.fnDecodeBase64(some_column) from Some_Table You can also use:
select convert(varchar(max),cast(N'' as xml).value('xs:base64Binary(sql:column("t.SomeColumn"))', 'varbinary(max)')) as converted_64_column from SomeTable as t The keywords: sql:column makes the difference
Comments
If you are trying to do this within Management Studio (or T-SQL directly), then you can accomplish it like this:
declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max) set @source = convert(varbinary(max), 'Hello Base64') set @encoded = cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)') set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)') select convert(varchar(max), @source) as source_varchar, @source as source_binary, @encoded as encoded, @decoded as decoded_binary, convert(varchar(max), @decoded) as decoded_varchar Reference: T-SQL: Easy Base64 Encoding and Decoding
