Substring RegExp Extract path

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