Skip to content

Instantly share code, notes, and snippets.

@mattm
Created May 15, 2018 13:13
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save mattm/8a8a09745bdd68f7353ac78172e45580 to your computer and use it in GitHub Desktop.
Standardize URL
SELECT
current_url,
CASE
WHEN STRPOS(current_url, "?") > 0 THEN SUBSTR(current_url, 0, STRPOS(current_url, "?") - 1)
WHEN STRPOS(current_url, "#") > 0 THEN SUBSTR(current_url, 0, STRPOS(current_url, "#") - 1)
ELSE current_url
END AS url
FROM (
SELECT 'https://www.helpscout.net/blog/beacon-preview-ui/' AS current_url
UNION ALL SELECT 'https://www.helpscout.net/blog/beacon-preview-ui/?utm_content=1234&utm_medium=social&utm_source=twitter' AS current_url
UNION ALL SELECT 'https://www.helpscout.net/blog/beacon-preview-ui/#example' AS current_url
);
+---------------------------------------------------------------------------------------------------------+---------------------------------------------------+
| current_url | url |
+---------------------------------------------------------------------------------------------------------+---------------------------------------------------+
| https://www.helpscout.net/blog/beacon-preview-ui/ | https://www.helpscout.net/blog/beacon-preview-ui/ |
| https://www.helpscout.net/blog/beacon-preview-ui/?utm_content=1234&utm_medium=social&utm_source=twitter | https://www.helpscout.net/blog/beacon-preview-ui/ |
| https://www.helpscout.net/blog/beacon-preview-ui/#example | https://www.helpscout.net/blog/beacon-preview-ui/ |
+---------------------------------------------------------------------------------------------------------+---------------------------------------------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment