SQL Regex to select string between second and third forward slash

Question

I am using Postgres/Redshift to query a table of URLs and am trying to use SELECT regex_substr to select a string that is between the second and third forward slash in the column.

For example I need the second slash delimited string in the following data:

/abc/required_string/5856365/
/abc/required_string/2/
/abc/required_string/l-en/
/abc/required_string/l-en/

Following some of the regexs in this this thread:

SELECT regexp_substr(column, '/[^/]*/([^/]*)/')
FROM table

None seem to work. I keep getting:

/abc/required_string/
/abc/required_string/

Answer 1

What about split_part?

SELECT split_part(column, '/', 3) FROM table

Example:

select split_part ('/abc/required_string/2/', '/', 3)

Returns: required string

Answer 2

This may work :

SQL Fiddle

PostgreSQL 9.3 Schema Setup:

CREATE TABLE t
    ("c" varchar(29))
;

INSERT INTO t
    ("c")
VALUES
    ('/abc/required_string/5856365/'),
    ('/abc/required_string/2/'),
    ('/abc/required_string/l-en/'),
    ('/abc/required_string/l-en/')
;

Query 1:

SELECT substring("c" from '/[^/]*/([^/]*)/')
FROM t

Results:

|       substring |
|-----------------|
| required_string |
| required_string |
| required_string |
| required_string |