varbinary in databricks and sql server

Question

Hi Expert, there is different output in varbinary in sql and databricks. it should be same

sql server value convert(varbinary(4),('0000000')) output '0x30303030' 

Databricks value cast select cast('0000000' as binary) output :  'MDAwMDAwMA=='

Answer 1

Shambhu Rai - Thanks for the question and using MS Q&A platform.

Here are the equivalent data type support & mapping for SQL Server sources:

For more details, refer to Azure Databricks - Data types.

Note: varbinary is not a supported data type in Azure Databricks.

Instead, you can use the binary data type in Databricks, which is equivalent to the varbinary data type in SQL Server. To convert a string to binary in Databricks, you can use the unhex function. For example, to convert the string '0000000' to binary, you can use the following code:

SELECT unhex('303030303030') as binary_value

Here is the equivalent code in Azure Databricks:

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

I am also facing the same issue, in SQL server

When I decode it in the databricks pyspark, I am not getting the correct values as in SQL server. I am using base64binary for encode/decode.

Decode in refined layer using

I have tried both HEX/base64binary approach. nothing is giving me the same values as that in sql server after decoding.

Any other solutions, that we can try to get the same values in Azure blob using databricks pyspark.