SELECT `justfunctions.eu.decode_url`("https%3A%2F%2Fjustdataplease.com%2Fjustfunctions-bigquery%2F%3Futm_campaign%3Dtest")
/*--Output--
https://justdataplease.com/justfunctions-bigquery/?utm_campaign=test
*/
CREATE OR REPLACE FUNCTION `your_project_id.your_dataset_id.decode_url`(`url` STRING)
RETURNS STRING AS (((
SELECT STRING_AGG(
IF(REGEXP_CONTAINS(y, r'^%[0-9a-fA-F]{2}'),
SAFE_CONVERT_BYTES_TO_STRING(FROM_HEX(REPLACE(y, '%', ''))), y), ''
ORDER BY i
)
FROM UNNEST(REGEXP_EXTRACT_ALL(url, r"%[0-9a-fA-F]{2}(?:%[0-9a-fA-F]{2})*|[^%]+")) y
WITH OFFSET AS i
)))
OPTIONS ( description = '''Decodes a <url>.''')
SQL User Defined Function (SQL UDF)
See something wrong? Contact us or report an issue on Github.