0

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.

S.R.
  • 3
  • 2
  • What SQL are you using - many will not support HTML character translations inherently. You probably want to look at the string functions for your version of SQL and see if it support str_replace – Paul Coldrey Feb 09 '18 at 06:18
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. –  Feb 09 '18 at 07:06
  • There is a solution here for you. – jwolf Feb 09 '18 at 07:42
  • What do you mean by "run properly"? Why should a database be concerned with url encoding? Both `&` and `%26` use plain ASCII characters – Hans Kesting Feb 09 '18 at 08:44
  • I'm using BigQuery. The auto-generated URLs are linked to a dashboard. The URL that has the ampersand (&) is displaying a dashboard that is empty when I know there's data in the dashboard. So that is why I want to convert the URL into a series of % and hex digits. Hope this further explains my question – S.R. Feb 09 '18 at 17:17

2 Answers2

0

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.

jwolf
  • 908
  • 7
  • 13
  • I think this will work. Thanks so much! Hopefully I'm not going to have many other characters to replace. – S.R. Feb 09 '18 at 17:49
  • You're welcome @S.R. - Please mark my answer as 'Accepted' if it helped you. Thanks. – jwolf Feb 09 '18 at 18:29
-1

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

Daniel Machet
  • 615
  • 1
  • 5
  • 7