Convert all hex in a string to its char value in Redshift
<p>In Redshift, I'm trying to convert strings like this:</p>

<pre><code>http%3A%2F%2Fwww.amazon.com%2FTest%3Fname%3DGary%26Bob
</code></pre>

<p>To look like this:</p>

<pre><code>http://www.amazon.com/Test?name=Gary&amp;Bob
</code></pre>

<p>Basically I need to convert all of the hex in a string to its char value. The only way I can think of is to use a regex function. I tried to do it in two different ways and received error messages for both:</p>

<pre><code>SELECT REGEXP_REPLACE(hex_string, '%([[:xdigit:]][[:xdigit:]])', CHR(x'\\1'::int))
ERROR: 22P02: "\" is not a valid hexadecimal digit

SELECT REGEXP_REPLACE(hex_string, '%([[:xdigit:]][[:xdigit:]])',CHR(STRTOL('0x'||'\\1', 16)::int))
ERROR: 22023: The input 0x\1 is not valid to be converted to base 16
</code></pre>

<p>The CHR and STRTOL functions works by itself. For example:</p>

<pre><code>SELECT CHR(x'3A'::int)
SELECT CHR(STRTOL('0x3A', 16)::int)
</code></pre>

<p>both returns</p>

<pre><code>:
</code></pre>

<p>And if I run the same pattern using a different function (other than CHR and STRTOL), it works:</p>

<pre><code>REGEXP_REPLACE(hex_string, '%([[:xdigit:]][[:xdigit:]])', LOWER('{H}'||'\\1'||'{/H}'))
</code></pre>

<p>returns</p>

<pre><code>http{h}3A{/h}{h}2F{/h}{h}2F{/h}www.amazon.com{h}2F{/h}Test{h}3F{/h}name{h}3D{/h}Gary{h}26{/h}Bob
</code></pre>

<p>But for some reason those functions won't recognize the regex matching group.</p>

<p>Any tips on how I can do this?</p>

<p>I guess the other solution is to use nested REPLACE() functions for all of the special hex characters, but that's probably a very last resort.</p>