How to convert symbols into ASCII (hex or dec) in BigQuery?

Question

URLs aren't translating. For ex. & needs to be translated in to %26 in order for the auto-generated url to populate data in a dashboard. I've tried cast, convert and to_code_points but all to no avail.

Answer 1

Use REPLACE (or it's equivalent), for instance:

select replace(@url_string, '&', '%26')

You can nest them to do multiple replacements, like this:

select replace(replace(@url_string, ' ', '%20'), '&', '%26')

For example:

select replace(replace('qwe&qwe&asd zxc zxc', ' ', '%20'), '&', '%26')

gives:

'qwe%26qwe%26asd%20zxc%20zxc'

I did this using SQL Server but any other SQL database will have a function that is very similar if not identical.

I hope this helps.

Answer 2

Dump() function will give you the characters encoded into one of a number of encodings e.g. decimal or hex