Thread: Substring RegExp Extract path

Hi,

I'm trying to extract element of a directory path stored in the db with substring

"/help/support/index/time.jsp"

and i want to extract the 1st, 2nd and 3rd parts

1st = help, 2nd = support, 3rd = index

but I can't get the regular expressions correct.

Any ideas on what I need to do.

select substring(data,'????') from paths

TIA

Nick wrote:
> "/help/support/index/time.jsp"
>
> and i want to extract the 1st, 2nd and 3rd parts
>
> 1st = help, 2nd = support, 3rd = index

Are you using 7.4? If so:

select split_part('/help/support/index/time.jsp','/',2),
        split_part('/help/support/index/time.jsp','/',3),
        split_part('/help/support/index/time.jsp','/',4),
        split_part('/help/support/index/time.jsp','/',5);
  split_part | split_part | split_part | split_part
------------+------------+------------+------------
  help       | support    | index      | time.jsp
(1 row)

See:
   http://www.postgresql.org/docs/current/static/functions-string.html

On Postgres 8.0.0beta1 you could also do:

select s.f[2], s.f[3], s.f[4]
  from (select string_to_array('/help/support/index/time.jsp','/') as f)
  as s;
   f   |    f    |   f
------+---------+-------
  help | support | index
(1 row)

HTH,

Joe

Joe Conway said:

> Nick wrote:
> > "/help/support/index/time.jsp"
> >
> > and i want to extract the 1st, 2nd and 3rd parts
> >
> > 1st = help, 2nd = support, 3rd = index
>
> Are you using 7.4? If so:
>
> select split_part('/help/support/index/time.jsp','/',2),
>         split_part('/help/support/index/time.jsp','/',3),
>         split_part('/help/support/index/time.jsp','/',4),
>         split_part('/help/support/index/time.jsp','/',5);
>   split_part | split_part | split_part | split_part
> ------------+------------+------------+------------
>   help       | support    | index      | time.jsp
> (1 row)
>
> See:
>    http://www.postgresql.org/docs/current/static/functions-string.html
>
> On Postgres 8.0.0beta1 you could also do:
>
> select s.f[2], s.f[3], s.f[4]
>   from (select string_to_array('/help/support/index/time.jsp','/') as f)
>   as s;
>    f   |    f    |   f
> ------+---------+-------
>   help | support | index
> (1 row)
>

Or if using the substring function in earlier versions:

select substring('/help/support/index/time.jsp','/(.*)/.*/.*/.*'),
         substring('/help/support/index/time.jsp','/.*/(.*)/.*/.*'),
         substring('/help/support/index/time.jsp','/.*/.*/(.*)/.*'),
         substring('/help/support/index/time.jsp','/.*/.*/.*/(.*)');

 substring | substring | substring | substring
-----------+-----------+-----------+-----------
 help      | support   | index     | time.jsp
(1 row)

Best regards,

Jim