Is it possible to convert hexadecimal to ASCII used Azure Data Factory?

Question

I would like to know if there is a possibility to convert hexadecimal to ASCII using Azure Data Factory?

Answer 1

@Peethambaran, S (Sreevidya) Thanks for using MS Q&A platform and posting your query.

Azure Data Factory (ADF) built-in expressions and functions do not currently support conversion from hex to string. However, you do have a couple of options:

1. If you have some nearby SQL compute (either Azure SQL DB, Azure Synapse Analytics, Managed Instance etc), you could call out to them via a Lookup activity. For example, you could use a SQL statement like this to convert hexadecimal to ASCII:

SELECT CAST( 0x737461636b6f766572666c6f77 AS VARCHAR(100) );

You could construct this statement as an expression like this:

@concat('SELECT CAST( ', variables('varInput'),' AS VARCHAR(100) );')

1. You could also use a notebook, for example, Databricks or Synapse Notebook
2. You could use derived column transformation in data flow or you can call the Azure function for converting the value

for more details refer :https://stackoverflow.com/questions/71213032/azure-data-factory-convert-hex-to-string

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-conversion-functions

Please note that these methods might not be the most efficient, especially if you have to convert large amounts of data. It’s always a good idea to consider the specific requirements and constraints of your project when choosing a method.

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.