need to parse up to 3rd / in a string by using regexp in sql

Question

when i am trying extract through regexp from the below source by using \|(.*?),1,2 this, i am getting up to end but i need to 3rd / only. when i use \|(.*/){1,3},1,2 it is giving http://localhost:6148372/content/bdsajf and i am stuck up at ending search position. please help me on this.

U=https://www.abcdf.com/aecb/app/login|http://llcwokhfkdvc.webs.com/sajc-services
U=http://localhost:7438/en.html|http://localhost:6148372/content/bdsajf/en/kjf-LKJf/FJKSF-cbxjs.html

O/P should be # http://llcwokhfkdvc.webs.com and http://localhost:6148372

Thanks

hwnd · Accepted Answer · 2013-10-07 11:52:28Z

1

Try the following expression:

\|([^/]*/*[^/]*)

See live demo

answered Oct 7, 2013 at 11:52

hwnd

70.9k4 gold badges100 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dileep Over a year ago

your logic is working but i am in a confusion how this picks http as well, can you explain how it behaves ?

hwnd Over a year ago

[^/]* matches everything up to the first / in // and then /* matches the two forward slashes, and [^/]* match everything up to the following /

Vincent Malgrat · Accepted Answer · 2013-10-07 11:43:58Z

0

Try '\|([^/]*/){1,3}' (11i):

SQL> SELECT regexp_substr(txt, '\|(([^/]*/){1,3})', 1, 1, 'i', '1') regexp
  2    FROM DATA;

REGEXP
--------------------------------------------------------------------------------
http://llcwokhfkdvc.webs.com/
http://localhost:6148372/

In 10g:

SQL> SELECT ltrim(regexp_substr(txt, '\|([^/]*/){1,3}'), '|') regexp
  2    FROM DATA;

REGEXP
--------------------------------------------------------------------------------
http://llcwokhfkdvc.webs.com/
http://localhost:6148372/

answered Oct 7, 2013 at 11:43

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

2 Comments

Przemyslaw Kruglej Over a year ago

I think the author didn't want the slash (/) sign at the end of the results.

Przemyslaw Kruglej Over a year ago

Neither can I, but he wrote what he wants to get: http://llcwokhfkdvc.webs.com and http://localhost:6148372 Just saying, cheers.

Przemyslaw Kruglej · Accepted Answer · 2013-10-07 11:49:07Z

0

You can try:

SELECT regexp_substr(t, '\|(http://.*?)/', 1, 1, NULL, 1)
  FROM (
    SELECT
      'U=https://www.abcdf.com/aecb/app/login|http://llcwokhfkdvc.webs.com/sajc-services' AS t
    FROM DUAL
    UNION
    SELECT
      'U=http://localhost:7438/en.html|http://localhost:6148372/content/bdsajf/en/kjf-LKJf/FJKSF-cbxjs.htmlO/P should be # http://llcwokhfkdvc.webs.com and http://localhost:6148372' AS t
    FROM DUAL
);

answered Oct 7, 2013 at 11:49

Przemyslaw Kruglej

8,1312 gold badges30 silver badges41 bronze badges

Collectives™ on Stack Overflow

need to parse up to 3rd / in a string by using regexp in sql

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related