urlencode with only built-in functions

Question

Without using plpgsql, I'm trying to urlencode a given text within a pgsql SELECT statement.

The problem with this approach:

select regexp_replace('héllo there','([^A-Za-z0-9])','%' || encode(E'\\1','hex'),'g')

...is that the encode function is not passed the regexp parameter, unless there's another way to call functions from within the replacement expression that actually works. So I'm wondering if there's a replacement expression that, by itself, can encode matches into hex values.

There may be other combinations of functions. I thought there would be a clever regex (and that may still be the answer) out there, but I'm having trouble finding it.

Answer 1

select regexp_replace(encode('héllo there','hex'),'(..)','%\\1','g');

This doesn't leave the alphanumeric characters human-readable, though.

Answer 2

You can use CLR and import the namespace or use the function shown in this link , this creates a T-SQL function that does the encoding.

http://www.sqljunkies.com/WebLog/peter_debetta/archive/2007/03/09/28987.aspx