Select with substring or regular expression

Hi,

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&amou nt=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)

Re: Select with substring or regular expression

Fritz Bayer wrote:

> Hi,
>
> 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&amou nt=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

Re: Select with substring or regular expression

Fritz,
Postgres' substring function handles regular expressions.
http://www.postgresql.org/docs/7.3/interactive/functions-str ing.html#FUNCTIONS-STRING-SQL
you'd want something like
SELECT substring(uri from [?]([^;]*) from fritz_table;
You might be able to do some fancy entity substitution, but if you just
want to replace spaces, you can do
SELECT replace(replace(substring(uri from [?]([^;]*), '%20', ' '), '&',
', ') from fritz_table;
hth
ion