Skip to content

bigfunctions > url_decode

url_decode

Call or Deploy url_decode ?

✅ You can call this url_decode bigfunction directly from your Google Cloud Project (no install required).

  • This url_decode function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
  • Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • You may prefer to deploy the BigFunction in your own project if you want to build and manage your own catalog of functions --> Read Getting Started. This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • For any question or difficulties, please read Getting Started.
  • Found a bug? Please raise an issue here

Public BigFunctions Datasets are like:

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...

Description

Signature

url_decode(url_encoded_string)

Description

Decode url_encoded_string (inspired from this stackoverflow solution)

Examples

select bigfunctions.eu.url_decode('http%3A%2F%2Fwww.example.com%2Fhello%3Fv%3D12345')

select bigfunctions.us.url_decode('http%3A%2F%2Fwww.example.com%2Fhello%3Fv%3D12345')

select bigfunctions.europe_west1.url_decode('http%3A%2F%2Fwww.example.com%2Fhello%3Fv%3D12345')

+--------------------------------------+
| string                               |
+--------------------------------------+
| http://www.example.com/hello?v=12345 |
+--------------------------------------+

Use cases

You have a table in BigQuery that stores URLs, but some of these URLs are URL-encoded (meaning special characters are replaced with percent signs followed by hexadecimal codes). You want to decode these URLs to their original, readable form.

Example Scenario:

Let's say you have a table named website_traffic with a column called encoded_url. This column contains URL-encoded strings like this:

'https%3A%2F%2Fwww.example.com%2Fproducts%3Fid%3D123%26source%3Dgoogle'

You can use the url_decode function to decode these URLs within a query:

SELECT url_decode(encoded_url) AS decoded_url
FROM `your_project.your_dataset.website_traffic`;

This query would produce a result set with a decoded_url column containing the properly decoded URLs:

https://www.example.com/products?id=123&source=google

Use Cases:

  • Log Analysis: Web server logs often store URLs in a URL-encoded format. Decoding them makes the logs more human-readable and easier to analyze.
  • Data Cleaning: If you have URL data from different sources, some might be encoded and some might not. Using url_decode ensures consistency in your data.
  • Reporting: Presenting decoded URLs in reports makes the information clearer and more understandable for stakeholders.
  • Data Integration: If you're integrating data from a system that provides URL-encoded URLs, you'll need to decode them before storing or processing them in BigQuery.

In essence, whenever you encounter URL-encoded strings in your BigQuery data and need to work with the actual URLs, the url_decode function becomes indispensable.