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.