[SOLVED] Does Pervasive Have a SQL Function for URL Encoding?

Does Pervasive Have a SQL Function for URL Encoding?

I've written a query that builds some URLs to an intranet site, but some of the URLs don't work because they contain special characters that need to be URL encoded. I'm trying to avoid writing a script (outside of SQL) to do the URL encoding; I'd like the database to do URL-Encoding instead, so that I can just export the data (as is) directly into csv file.

For example, I can encode just one character quite easily. Here, I encode & to %26:

select
     customer_id 
    ,customer_name
    ,'https://intranet.local/customer/?id=' + replace(customer_id,'&','%26') as url
from customer

However, this method becomes quite verbose when encoding multiple characters.

Is there a function in Pervasive 13 that will do URL-encoding?

Solution

Based on the answer give here, you can create a function using:

CREATE FUNCTION urlencode(:description char(200))
RETURNS char(200)
AS
BEGIN
  SELECT 
   Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
   Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
   Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
   Replace(RTRIM(:description)
       ,'%','%25')       ,'&','%26')       ,'$','%24')       ,'+','%2B')
       ,',','%2C')       ,':','%3A')       ,';','%3B')       ,'=','%3C')
       ,'?','%3D')       ,':','%3F')       ,'@','%40')       ,'#','%23')
       ,'<','%3C')       ,'>','%3E')       ,'[','%5B')       ,']','%5D')
       ,'{','%7B')       ,'}','%7D')       ,'|','%7C')       ,'^','%5E')
       ,' ','%20')       ,'~','%7E')       ,'`','%60')       ,'*','%2A')
       ,'(','%28')       ,')','%29')       ,'/','%2F')       ,'\\','%5C')
       ,' ','%20') INTO :description;
  RETURN :description;
END;