I'm dealing with a table that stores URLs. One field contains the host
name and another contains the URI.
Now I would like to create some scripts to extract certain the keys
and values of the query part of the URI.
If the uri field of a select contains:
/dataHandler?name=fritz&lastname=schmitt%20meier&age=45&amount=45;jsessionid=23455
then my select statement should only display
name=fritz&lastname=schmitt%20meier&age=45&amount=45
or preferable
name=fritz, lastname=schmitt meier, age=45, amount=45
Now I was wondering if somebody knows some functions on how I could
achieve this? Are there any regular expression aware functions?
Could somebody post a select statement, which extracts those parts and
rewrite the result (it would also have to decode uri specific
characters like %20 %hexhex etc)
Well, something like this would get you to the first one:
SELECT split_part(split_part(the_url, '?', 2), ';', 1);
You could use replace() to change the '&' to ', '. But, changing the %XX to
the character it represents would be a little more involved--I'm not aware
of a built in function that does it. You'd probably either have to write
you own function that does it directly, or write a conversion (similar to
ascii_to_utf_8) and use convert().
Stu