SQL Server函数或过程，用于根据其他表中的参数从表格中剪切记录

Question

我有两个表，我应该在这两个表之间进行比较，第一个表有一列此列是完整的URL，另一个表有两列第一列是URLCategory，另一个是the number，我应该在另一个表列URL中剪切多少/

第一个表是

                          URL
http://10.6.2.26/ERP/HRServices/WorkflowService.asmx
http://195.170.180.170/SADAD/PaymentNotificationService.asmx
http://10.6.2.26/ERP/HRServices/WorkflowService.asmx
http://10.6.2.26/ERP/HRServices/WorkflowService.asmx
http://10.6.2.26/ERP/HRServices/WorkflowService.asmx
http://217.146.8.6/din.aspx?s=11575802&client=DynGate&p=10002926
http://195.170.180.170/SADAD/PaymentNotificationService.asmx
http://10.6.2.26/ERP/HRServices/WorkflowService.asmx
http://195.170.180.170/SADAD/PaymentNotificationService.asmx
http://www.google.com/

第二个表应该与

  URL                              CUT_BEFORE
http://10.6.2.26                  3
http://217.146.8.6                1
http://195.170.180.170            2

我应该在第二个表和第一列之间做这样的比较

  URL                        
http://10.6.2.26/ERP/HRServices
http://195.170.180.170/SADAD
http://10.6.2.26/ERP/HRServices
http://10.6.2.26/ERP/HRServices
http://10.6.2.26/ERP/HRServices
http://217.146.8.6
http://195.170.180.170/SADAD
http://10.6.2.26/ERP/HRServices
http://195.170.180.170/SADAD
http://www.google.com/

在SQLServer中执行类似操作的函数脚本是什么

或者我们可以在存储过程中使用while循环，因为当我尝试执行下面的最后一个函数时，我使用了这个查询

declare @table table
( main_url  NVARCHAR(MAX),URL NVARCHAR(MAX), count int)
insert @TABLE
select 
Main_URL,T2.Url,T2.[Count]
from 
(select 
URL as Main_URL,LEFT(URL1, CHARINDEX('/', URL1) - 1) as  URL1
from
(select URL,replace(stuff(URL1, 1,patindex('%://%', URL1 + '0'), ''),'//','') as URL1
from (select URL, convert(nvarchar(max),[Url]) Url1 from [dbo].[InternetUsage_nn] )T1)T)T1
left outer join [dbo].[InternetUsage_URL_List] T2
on T1.URL1=convert(nvarchar(max),T2.URL) where T2.URL is not null

select dbo.FindAbsolutePath('/',Main_url,count) from @Table

等待你的回答

谢谢

Answer 1

下面的代码在Split函数的帮助下执行您需要的操作：

CREATE FUNCTION dbo.Split(@StringToSplit NVARCHAR(MAX), @Delimiter NCHAR(1))
RETURNS TABLE
AS
RETURN
(   
    SELECT  ID = ROW_NUMBER() OVER(ORDER BY n.Number),
            Position = Number,
            Value = SUBSTRING(@StringToSplit, Number, CHARINDEX(@Delimiter, @StringToSplit + @Delimiter, Number) - Number)
    FROM    (   SELECT  TOP (LEN(@StringToSplit) + 1) Number = ROW_NUMBER() OVER(ORDER BY a.object_id)
                FROM    sys.all_objects a
            ) n
    WHERE   SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, n.Number, 1) = @Delimiter
);

一旦你有了这个函数，你的代码就变得相对简洁了：

DECLARE @T TABLE (URL VARCHAR(1000));
DECLARE @T2 TABLE (URL VARCHAR(1000), Cut_Before INT);
-- POPULATE TABLES HERE (NOT INCLUDED TO SAVE SPACE)

WITH CTE AS
(   SELECT  FullURL = t.URL,
            BaseURLLength = LEN(ISNULL(t2.URL, t.URL)),
            Remainder = ISNULL(REPLACE(t.URL, t2.URL, ''), ''),
            Cut_Before = ISNULL(t2.Cut_Before, 1)
    FROM    @T AS t
            LEFT JOIN @T2 AS t2
                ON t.URL LIKE t2.URL + '/%'
)
SELECT  t.FullURL,
        Cut = SUBSTRING(t.FullURL, 1, BaseURLLength + LEN(s.Value) + s.Position - 1)
FROM    CTE t
        OUTER APPLY dbo.Split(t.Remainder, '/') AS s
WHERE   s.ID = t.Cut_Before;

前提是，CTE中的第一部分通过使用LIKE连接来标识每个完整URL的部分url。以http://10.6.2.26/ERP/HRServices/WorkflowService.asmx为例，它将显示以下内容：

FullURL:        http://10.6.2.26/ERP/HRServices/WorkflowService.asmx            
BaseURLLength:  16
Remainder:      /ERP/HRServices/WorkflowService.asmx
Cut_Before:     3

其中，剩余部分是删除部分URL后完整url的剩余部分。然后，split函数将剩余部分拆分为它的每个组成部分的新行：

SELECT *
FROM dbo.Split('/ERP/HRServices/WorkflowService.asmx', '/');

将返回：

ID  Position    Value
1   1   
2   2           ERP
3   6           HRServices
4   17          WorkflowService.asmx

然后，这仅限于与Cut_Before值匹配的行。然后可以使用此行建立位置，以“剪切”完整的URL (起始位置+该位置上的值的长度)。

Answer 2

我修改了我的代码。此代码块将解决您的问题。

CREATE Function FindAbsolutePath(
 @TargetStr varchar(8000), 
 @SearchedStr varchar(8000), 
 @Occurrence int
)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @Result varchar(8000);

    if CHARINDEX('http://',@SearchedStr)>0 --fix http://
    BEGIN
        set @Occurrence=@Occurrence+2;
    END


    ;WITH Occurrences AS (
            SELECT 
            Number,
            ROW_NUMBER() OVER(ORDER BY Number) AS Occurrence
            FROM master.dbo.spt_values
            WHERE 
            Number BETWEEN 1 
            AND LEN(@SearchedStr) 
            AND type='P'
            AND SUBSTRING(@SearchedStr,Number,LEN(@TargetStr))=@TargetStr
    )



    SELECT @Result= SUBSTRING(@SearchedStr,0,Number)
    FROM Occurrences
    WHERE Occurrence=@Occurrence 
    return @Result
END

--select dbo.FindAbsolutePath('/','http://10.6.2.26/ERP/HRServices/WorkflowService.asmx',3)