只有在SQL服务器获得网址的一部分(Get url part only in SQL Server)

我的表数据的样子：

SessionRefere  

http://www.google.com/url?sa=t&rct=j&q=aaa bbb&source=web&cd=1&cad=rja&sqi=2&ved=0CB4QFjAA&url=http://www.abc.com/&ei=QFR0UM-JKIrQrQfsuoG4CQ&usg=AFQjCNExYcKkcvobBbktLGNksptf1giQRw
-------------------------------------------------
http://www.google.com/reader/view/?hl=es&tab=Xq&at=RTknd_lBUUnvNqan2641EA
----------------------------------------------------
http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&cad=rja&ved=0CEgQFjAE&url=http://www.abc.com/ppp.aspx&ei=dmd0UOO9AYnY4QS1sYGwBw&usg=AFQjCNGIFcJUUSVpl_ZiZoSWDP2LkIagtw

这只是品尝data.There很多情况下是这样的。 所以，我想计数之前原始地址的一部分。

我用这个查询来获取数据。

DECLARE @temp table(RefPage nvarchar(200))
insert into @temp
SELECT
   CASE CHARINDEX( '?', SessionReferer)
       WHEN 0 THEN SessionReferer
       ELSE LEFT(SessionReferer, CHARINDEX( '?', SessionReferer) - 1) END AS RefPage 
FROM Tracker WHERE [start] between '1-1-2010' and '1-1-2015' and  SessionReferer<> ''

 select distinct RefPage, count(*) as [VisitTime] from @temp
  group by RefPage
order by [VisitTime] desc

我的结果是：

SessionRefere                           VisitTime
http://www.google.com/url                 2
http://www.google.com/reader/view/        1 

但我想结果是：

SessionRefere                           VisitTime
http://www.google.com                      3

是否有可能获得渴望的结果还是我在错误的方式？ 谢谢。

Answer 1:

在尝试分裂CHARINDEX('/',SessionReferrer,9)

 select 
   case when CHARINDEX('/',SessionReferrer,9) = 0 
     then SessionReferrer
     else left(SessionReferrer, CHARINDEX('/',SessionReferrer,9)-1)
   end,
   count(*)
 from Tracker      
 group by
   case when CHARINDEX('/',SessionReferrer,9) = 0 
     then SessionReferrer
     else left(SessionReferrer, CHARINDEX('/',SessionReferrer,9)-1)
   end

（我懒洋洋地假定所有的引荐以http：//或https：//）

Answer 2:

尝试这个

Declare @T Table(URL Varchar(MAX))
Insert Into @T Values('http://www.google.com/url?sa=t&rct=j&q=aaa bbb&source=web&cd=1&cad=rja&sqi=2&ved=0CB4QFjAA&url=http://www.abc.com/&ei=QFR0UM-JKIrQrQfsuoG4CQ&usg=AFQjCNExYcKkcvobBbktLGNksptf1giQRw')
Insert Into @T Values('http://www.google.com/reader/view/?hl=es&tab=Xq&at=RTknd_lBUUnvNqan2641EA')
Insert Into @T Values('http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&cad=rja&ved=0CEgQFjAE&url=http://www.abc.com/ppp.aspx&ei=dmd0UOO9AYnY4QS1sYGwBw&usg=AFQjCNGIFcJUUSVpl_ZiZoSWDP2LkIagtw')
Insert Into @T Values('http://www.yahoo.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&cad=rja&ved=0CEgQFjAE&url=http://www.abc.com/ppp.aspx&ei=dmd0UOO9AYnY4QS1sYGwBw&usg=AFQjCNGIFcJUUSVpl_ZiZoSWDP2LkIagtw')

;WITH CTE AS
(
    SELECT 
        URL
        ,SessionRefere = SUBSTRING(URL,0,CHARINDEX('://', URL)+3) + SUBSTRING(URL,CHARINDEX('://', URL)+3,CHARINDEX('/', SUBSTRING(URL,CHARINDEX('://', URL)+3,LEN(URL)))-1)
    FROM @T
)

SELECT SessionRefere,[VisitTime] = COUNT(SessionRefere)
FROM CTE
GROUP BY SessionRefere

产量

SessionRefere   VisitTime
http://www.google.com   3
http://www.yahoo.com    1

Answer 3:

在这里，我做的：

一个字符串@Test：
我从URL分裂“//”和“/”之间的字符串和concatinating http：或HTTPS根据给定的URL

Declare  @test nvarchar(100)
Declare @http nvarchar(8)
set @test = 'http://www.google.com/url?sa=t&rct=j&q=aaa bbb&source=web&cd=1&cad=rja&sqi=2&ved=0CB4QFjAA&url=http://www.abc.com/&ei=QFR0UM-JKIrQrQfsuoG4CQ&usg=AFQjCNExYcKkcvobBbktLGNksptf1giQRw'
declare @length int
declare @start int
declare @subString nvarchar(100)
set @start =( select CharIndex('//', @test))
set @http = SUBSTRING(@test, 0 , @start)
set @length = Len(@test)
set @subString = SUBSTRING ( @test ,@start+2 , @length )
set @start =( select CharIndex('/', @subString))
set @subString = SUBSTRING ( @subString ,0 , @start )
set @subString = @http+'//'+ @subString

文章来源: Get url part only in SQL Server