Search code examples
sqldremel

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

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.

Solution

  • 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.