trim a strim then group by them in an MS SQL query

[blackrabbit]

Okay here is my situation...

Our security on our network is very locked down. If a server needs web access to download an app or get updates etc, we have firewall rules that allow that server to that specific site.

Not a big deal for the most part. Right now our patch management system goes and gets its patches for part of it's system from a specific domain. HOWEVER to get patches that it can install for other vendors it keeps a database of the exact location of that patch, for example

vendor1patch1 -

http://www.vendor1.com/a/patch1.....

vendor1patch2 -

http://www.vendor1.com/a/patch2.....

vendor2patch1 -

http://www.vendor2.com/a/patch1.....

There are over 70k of records in that table for all the patches it can install for all the software it supports. This table gets updated monthly when new patches come out and occasionally a new vendor domain might be added and we’d have to add it to our firewall rules.
My dilemma is making a report from this table so we can present it to our firewall group at meetings so we can say “Oh we have some more sites to add to the firewall rules”

I’m looking for a way to somehow to create a view in sql 2005 that will:

1. Truncate all the addresses in a query by selecting everything to the left of the third “/” so I only get

http://www.vendor.com

2. Then group the query by them so instead of 70k worth of rows we have maybe 30 or 40 rows giving me a list of just unique domain names.

So far my thought is first create a view that does step 1 then another view that does step two and then I can import that second view into excel or whatever.

My main problem is coming up with a query that will cut everything from the left of the third “/”, that is where I am stuck. I can do it in excel and vb.net but I’m trying to build a few views in sql to do most of it for me. Any ideas? Thanks

 

[markros]

Try:
select substring(myColumn,1,patindex('%[//]%[/]%',myColumn))

---------
Not tested.

PluralSight Learning Library

 

[blackrabbit]

Thanks, I made some small changes and used the same basic substring portion in my group by and it worked like a charm. It reduced 70k rows of specific http locations to 89 rows of domain names which will make our task a lot easier.