Oracle - Group By and split

I like to get a group by all ip addresses and ports.

this is my select script:

SELECT Count(p.url) nr, SUBSTR(p.url, 8, 19) as IpPort
FROM pages p
GROUP BY SUBSTR(p.url, 8, 19)
Order By nr DESC 


Table data:
url
http://10.0.0.10:123/test/foo/bar
http://10.0.0.10:456/tasdfest/foasdfo/baasdfr
http://localhost:125/asdf/
etc.

This script works with ip addresse but not with the string "localhost".

How can I get all IP开发者_开发技巧 addresses and ports incl. localhost (without http:// and the /)?

Any help would be appreciated.

thanks!

There are probably at least three ways of finding this off the top of my head.

Firstly, there is the traditional way using the combination of instr and substr

substr(url, 8, instr(url, '/',1,3)-8) as ipport

Then there are a couple of different ways using the regular expression functions. These examples are matching everything that isn't a slash character / so if the port number isn't followed by a slash then there might be problems.

regexp_substr(url, '([^/]+)',8)

regexp_replace(url, 'http://([^/]+)/.*', '\1')

I think I probably prefer the regexp_substr version although if your data volumes are high, it might pay to stick with the substr/instr option as in my experience under Oracle 10g the regexp functions aren't that speedy.

As with any text matching operations, make sure that your code matches all possible data.

You could use REGEXP_SUBSTR() to find the diffrent parts then group by this

see: http://download.oracle.com/docs/cd/B13789_01/server.101/b10759/functions116.htm

build your REGEX using the help of

http://regexpal.com/

Use INSTR() to find the index of the third slash:

SELECT Count(p.url) nr, SUBSTR(p.url, 8, instr(p.url, '/', 8)) as IpPort
FROM pages p
GROUP BY SUBSTR(p.url, 8, 19)
Order By nr DES

Only works if they all start with http://