Converting base64Binary to UTF-8 (String) in SQL Server 2008

9/22/2016

9/14/2016

Try below

SELECT bd.data_timestamp
       ,bd.*
       ,wbd.*
       ,CAST(CAST(wbd.DATA_TYPE as sql_variant ).value('.','varbinary(max)') AS varchar(max) )

FROM MSGS bd, WMB_data_event wbd
WHERE bd.MSGFLOW_NAME = 'TestDataFlow'
       AND bd.data_timestamp > '2016-01-10 13:30:00'
       AND bd.data_timestamp < '2016-01-10 18:30:00'
       AND bd.TERMINAL_NAME = 'pass'
       AND bd.WMB_MSGKEY = wbd.WMB_MSGKEY
       AND wbd.DATA_TYPE = 10

ORDER BY bd.data_timestamp ASC

9/14/2016

Also can you send a row which is giving error ?

9/14/2016

ASKER

Hi Pawan,

Your code gives me the error:

Msg 258, Level 15, State 1, Line 5
Cannot call methods on sql_variant.

There is no row I know of which is giving me the error.

Thank you.

9/14/2016

Could you please send few rows from column wbd.DATA_TYPE in a excel file, so that i can review.?

9/14/2016

ASKER

Hi Pawan,

Can't do that as the data itself is 'insurance data' hence a little sensitive. I can tell you the data can be decoded successfully using the 'https://www.base64decode.org/' site.

Let me know if there is anything else you would like to know about the data, though.

Thank you.

9/14/2016

ASKER

Hi Pawan,

Attached is file showing the column properties in SQL server.

Thank you.
DATA_TYPE-Columns.PNG

9/14/2016

ASKER

Hi Pawan,

Using the function found on this web page:

http://www.jrevell.com/base64-encoding-and-decoding-with-sql-server-unicode-characters/

Convert from Base64

CREATE FUNCTION [dbo].[fn_str_FROM_BASE64]
(
    @BASE64_STRING NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT 
            CAST(
                CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@BASE64_STRING"))', 'VARBINARY(MAX)') 
            AS NVARCHAR(MAX)
            )   UTF8Encoding
    )
END

My query below:

SELECT bd.data_timestamp
       ,bd.*
       ,wbd.*
       ,MB.dbo.fn_str_FROM_BASE64(wbd.DATA_TYPE) AS Converted_Value
FROM MSGS bd, WMB_data_event wbd
WHERE bd.MSGFLOW_NAME = 'TestDataFlow'
       AND bd.data_timestamp > '2016-01-10 13:30:00'
       AND bd.data_timestamp < '2016-01-10 18:30:00'
       AND bd.TERMINAL_NAME = 'pass'
       AND bd.WMB_MSGKEY = wbd.WMB_MSGKEY
       AND wbd.DATA_TYPE = 10

ORDER BY bd.data_timestamp ASC

The 'Converted Value' returns a null value if this provides further insights.

Thank yo.

9/14/2016

Great ! can you please mark one solution as accepted solution and close it..

9/14/2016

ASKER

Hi Pawan,

No solution yet : The 'Converted Value' returns a null value if this provides further insights.

Let me know if you think of anything.

9/14/2016

At least could you please one value of wbd.DATA_TYPE which is getting converted to NULL value.

9/14/2016

ASKER

HI Pawan,

Generated some test output hopefully the format helps:

C01TSHxeflwmfEFJU1RFQ0h8T0N8V0laQVJEfFRFU1RDRU5UUkV8MjAxNjA5MTEwMjAxMjh8U0VD
VVJFfERGVF5QMDN8UlhDLTAwMTUwNjg5NDl8UHwyLjINCkVWTnxQMDN8MjAxNjA5MTEwMjAxMjgN
ClBJRHwxfDc4OTQ1NDB8NDUzNjcyNDM0fDg5NzIzNDYtRklOfFNUIFphY2teQnJvd258fDE5OTAw
ODAyMDAwMDAwMDB8TXx8fHx8fHx8fHw3ODk0NTQwDQpQQjJ8fHxURVNUVVNFUnx8fHwoMjM2MzY0
QkIpIEFsZXgNCkZUMXx8Nzg0MDM4ODl8fDIwMTYwOTExMDIwMTI2MDB8fEl8T05EQTMwMnx8fDF8
fHx8fHx8fHx8QUFNMzIxNA0KWlBNfFZ8QUJDREV8TUFUUklYfCB8MTA0MXxPTkRBMzAyfE9NTklU
RVNUREFUQXw2fCB8MjcuMDB8MS4wMHw4NzQ2NjU0MzZ8QUFNMzIxNHwgfCB8MjcuMDB8IHwgfCB8
VEVTVFVTRVJ8IHw0MHwxMHwyMDE2MDkxMTAyMDEyNjAwDQpaUEJ8UmVmZXJhbHw4Nzk0NTQzNDN8
OCBIUkxZIChTRVFVRU5DRSl8UE98dGFiLWRpcw==

Thank you

9/14/2016

ASKER

Hi Pawan,

The above is getting a NULL value.

Thank you.

9/14/2016

wait let me check .

Zberteoc

9/14/2016

I tried the function fn_str_FROM_BASE64 with the Base64 string that was pested and I got some chinese text:

䴋䡓幼屾簦䥁呓䍅籈䍏坼婉剁籄䕔呓䕃呎䕒㉼㄰〶ㄹ〱〲㈱簸䕓啃䕒䑼呆偞㌰剼䍘〭㄰〵㠶㐹簹籐⸲ല䔊乖偼㌰㉼㄰〶ㄹ〱〲㈱സ倊䑉ㅼ㝼㤸㔴〴㑼㌵㜶㐲㐳㡼㜹㌲㘴䘭义卼⁔慚正䉞潲湷籼㤱〹㠰㈰〰〰〰〰䵼籼籼籼籼籼㠷㐹㐵ര倊㉂籼呼卅啔䕓籒籼⡼㌲㌶㐶䉂 汁硥਍呆簱㝼㐸㌰㠸簹㉼㄰〶ㄹ〱〲㈱〶簰䥼佼䑎㍁㈰籼ㅼ籼籼籼籼籼䅁㍍ㄲഴ娊䵐噼䅼䍂䕄䵼呁䥒籘簠〱ㄴ佼䑎㍁㈰佼乍呉卅䑔呁籁簶簠㜲〮簰⸱〰㡼㐷㘶㐵㘳䅼䵁㈳㐱⁼⁼㉼⸷〰⁼⁼⁼呼卅啔䕓籒簠〴ㅼ簰〲㘱㤰ㄱ㈰㄰㘲〰਍做籂敒敦慲籬㜸㐹㐵㐳簳‸剈奌⠠䕓啑久䕃簩佐瑼扡搭獩

I am using SQL 2012, the asker is using SQL 2008. It might be a version issue?

Awsome functions, by the way!

9/15/2016

ASKER

Hi Zberteoc,

Cheers for that although they are not mine. Thank Google.

Zberteoc

9/15/2016

What do you mean they are not yours? I copied the Base64 text you posted above and that is what the function returned wen applied to it, the Chinese text while you were complaining it returned NULL. Can you explain what you meant?

9/15/2016

ASKER

Hi Zbertoc,

The base64 text is what I am trying to read that's mine. The function though I am using isn't check the link: http://www.jrevell.com/base64-encoding-and-decoding-with-sql-server-unicode-characters/

Thank you.

Zberteoc

9/16/2016

But is it returning a NULL value for that Base64 string or not?

9/17/2016

ASKER

Hi Zbertoc,

Yes it's returning a NULL value for the Base64 string.

Thank you.

9/22/2016

Well... you are going to have some problems with UTF-8 because SQL doesn't know about that, but converting from base64 is easy enough. You might be getting NULLS because of zero byte Unicode, or, non-ASCII characters within the base64 encoding.

Using your sample above...

declare @str varchar(max);   -- could be nvarchar

set @str = 'C01TSHxeflwmfEFJU1RFQ0h8T0N8V0laQVJEfFRFU1RDRU5UUkV8MjAxNjA5MTEwMjAxMjh8U0VD
VVJFfERGVF5QMDN8UlhDLTAwMTUwNjg5NDl8UHwyLjINCkVWTnxQMDN8MjAxNjA5MTEwMjAxMjgN
ClBJRHwxfDc4OTQ1NDB8NDUzNjcyNDM0fDg5NzIzNDYtRklOfFNUIFphY2teQnJvd258fDE5OTAw
ODAyMDAwMDAwMDB8TXx8fHx8fHx8fHw3ODk0NTQwDQpQQjJ8fHxURVNUVVNFUnx8fHwoMjM2MzY0
QkIpIEFsZXgNCkZUMXx8Nzg0MDM4ODl8fDIwMTYwOTExMDIwMTI2MDB8fEl8T05EQTMwMnx8fDF8
fHx8fHx8fHx8QUFNMzIxNA0KWlBNfFZ8QUJDREV8TUFUUklYfCB8MTA0MXxPTkRBMzAyfE9NTklU
RVNUREFUQXw2fCB8MjcuMDB8MS4wMHw4NzQ2NjU0MzZ8QUFNMzIxNHwgfCB8MjcuMDB8IHwgfCB8
VEVTVFVTRVJ8IHw0MHwxMHwyMDE2MDkxMTAyMDEyNjAwDQpaUEJ8UmVmZXJhbHw4Nzk0NTQzNDN8
OCBIUkxZIChTRVFVRU5DRSl8UE98dGFiLWRpcw==
';

select convert(varchar(max),cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(max)'));

And gives the result :

MSH|^~\&|AISTECH|OC|WIZARD|TESTCENTRE|20160911020128|SECURE|DFT^P03|RXC-0015068949|P|2.2
EVN|P03|20160911020128
PID|1|7894540|453672434|8972346-FIN|ST Zack^Brown||1990080200000000|M||||||||||7894540
PB2|||TESTUSER||||(236364BB) Alex
FT1||78403889||2016091102012600||I|ONDA302|||1||||||||||AAM3214
ZPM|V|ABCDE|MATRIX| |1041|ONDA302|OMNITESTDATA|6| |27.00|1.00|874665436|AAM3214| | |27.00| | | |TESTUSER| |40|10|2016091102012600
ZPB|Referal|879454343|8 HRLY (SEQUENCE)|PO|tab-dis

So, to test, set your @str to a selected (single) instance from your table, and run the xquery.

If you can let me know your results... We can then modify the query as appropriate and use the sql:column in the xquery instead of sql:variable (and maybe the use of Unicode) But base64 is meant to be ascii in nature.

9/22/2016

ASKER

Hi Mark,

That query you gave me works perfectly when you able kindly show me to modify the original query to give the expected results.

Thank you.

ASKER CERTIFIED SOLUTION

9/22/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

9/22/2016

ASKER

Thank you for the help that query works (Very slowly) and I adapted it to original query.

aikimark

9/22/2016

@Mark

Very clever. If speed is an issue, could a temp table solve the performance issue?

9/22/2016

ASKER

Hi Aikimark,

Good point I will try that tomorrow.

Cheers.