生成动态PATINDEX语句

Question

我有以下两个字符串：

DECLARE @Str1 VARCHAR(MAX) = 'John A Mak|Street Road UAE'
DECLARE @Str2 VARCHAR(MAX) = '[First Name],[Last Name],[Middle Name]|[Address1],[Address2]'

注意:这两个字符串都是动态的，可能附带或多或少的值。

预期结果:希望使用PATINDEX显示给定列中的任何给定文本。下面的PATINDEX语句将在SELECT语句的WHERE子句中使用。

PATINDEX('John',[First Name]) + PATINDEX('A',[First Name]) + PATINDEX('Mak',[First Name]) + 
PATINDEX('John',[Last Name]) + PATINDEX('A',[Last Name]) + PATINDEX('Mak',[Last Name]) +
PATINDEX('John',[Middle Name]) + PATINDEX('A',[Middle Name]) + PATINDEX('Mak',[Middle Name]) +
PATINDEX('Street',[Address1]) + PATINDEX('Road',[Address1]) + PATINDEX('UAE',[Address1]) +
PATINDEX('Street',[Address2]) + PATINDEX('Road',[Address2]) + PATINDEX('UAE',[Address2]) > 0

我的尝试：

DECLARE @Str1 VARCHAR(MAX) = 'John A Mak|Street Road UAE'
DECLARE @Str2 VARCHAR(MAX) = '[First Name],[Last Name],[Middle Name]|[Address1],[Address2]'
DECLARE @Length int = 0
DECLARE @Length1 int = 0
DECLARE @Length2 int = 0
DECLARE @Position int = 0
DECLARE @Position1 int = 0
DECLARE @Position2 int = 0
DECLARE @Value varchar(max)
DECLARE @Value1 varchar(max)
DECLARE @Value2 varchar(max)
DECLARE @P_Str2 VARCHAR(MAX) = ''
DECLARE @P_Str3 VARCHAR(MAX) = ''
DECLARE @P_Str1 VARCHAR(MAX) = ''
DECLARE @FinalString VARCHAR(MAX) = ''

SET @P_Str1 = @Str1+'|';
SET @P_Str3 = @Str2+'|';

IF OBJECT_ID('tempdb..#tempt', 'U') IS NOT NULL DROP TABLE #tempt;
CREATE TABLE #tempt(keywords varchar(max));


WHILE CHARINDEX('|', @P_Str3, @Position2+1)>0
BEGIN
    set @Length2 = CHARINDEX('|', @P_Str3, @Position2+1) - @Position2
    set @Value2 = SUBSTRING(@P_Str3, @Position2, @Length2)

    SET @P_Str2 = @Value2+',';

    PRINT('--'+@P_Str2);
    --WHILE LOOP for creating string for PAT INDEX
    WHILE CHARINDEX(',', @P_Str2, @Position+1)>0 
    BEGIN
        set @Length = CHARINDEX(',', @P_Str2, @Position+1) - @Position
        set @Value = SUBSTRING(@P_Str2, @Position, @Length)

        WHILE CHARINDEX('|', @P_Str1, @Position1+1)>0
        BEGIN
            set @Length1 = CHARINDEX('|', @P_Str1, @Position1+1) - @Position1
            set @Value1 = SUBSTRING(@P_Str1, @Position1, @Length1)  

            PRINT('Value1--'+@Value1);
            PRINT('Value--'+@Value);
            INSERT INTO #tempt 
                SELECT DISTINCT split.a.value('.', 'VARCHAR(100)') AS Keywords
                FROM
                (
                    SELECT CAST ('<S>' + REPLACE(ltrim(rtrim(@Value1)), ' ', '</S><S>') + '</S>' AS XML) AS Element 
                ) AS a
                CROSS APPLY Element.nodes ('/S') AS split(a)
                WHERE split.a.value('.', 'VARCHAR(100)') <> ''; 

                SET @FinalString  += STUFF(( SELECT '(PATINDEX('''+keywords+''','+@Value+'),''''0'''') + '
                                             FROM #tempt FOR XML PATH('')), 1,0, '');   

                DELETE FROM #tempt;

            SET @Position1 = CHARINDEX('|', @P_Str1, @Position1+@Length1) +1
        END

        SET @Position = CHARINDEX(',', @P_Str2, @Position+@Length) +1

    END 

    SET @Position2 = CHARINDEX('|', @P_Str3, @Position2+@Length2) +1

END 
PRINT(@FinalString);

但无法得到预期的结果。

Answer 1

这不是很动态的SQL，但是..。

USE Sandbox;
DECLARE @Str1 VARCHAR(MAX) = 'John A Mak|Street Road UAE';
DECLARE @Str2 VARCHAR(MAX) = '[First Name],[Last Name],[Middle Name]|[Address1],[Address2]';

DECLARE @WHERE nvarchar(MAX);
SET @WHERE = STUFF((SELECT N' + ' + NCHAR(10) + 
                         STUFF((SELECT N' + ' +NCHAR(10)+
                                       N'PATINDEX(' + QUOTENAME(DSn.Item,'''') + N',' + DSc.Item + N')' --This trusts no injection.I don't like this.
                                FROM dbo.DelimitedSplit8K(DS1.Item,' ') DSn
                                     CROSS APPLY dbo.DelimitedSplit8K(DS2.Item,',') DSc
                                ORDER BY DSc.ItemNumber, DSn.ItemNumber
                                FOR XML PATH(N'')),1,4,N'')
                  FROM dbo.DelimitedSplit8K (@Str1,'|') DS1
                       CROSS APPLY dbo.DelimitedSplit8K (@Str2,'|') DS2
                  WHERE DS1.ItemNumber = DS2.ItemNumber
                  ORDER BY DS1.ItemNumber
                  FOR XML PATH(N'')),1,4,N'') + N' > 0'

SELECT @WHERE;

这一产出如下：

PATINDEX('John',[First Name]) + 
PATINDEX('A',[First Name]) + 
PATINDEX('Mak',[First Name]) + 
PATINDEX('John',[Last Name]) + 
PATINDEX('A',[Last Name]) + 
PATINDEX('Mak',[Last Name]) + 
PATINDEX('John',[Middle Name]) + 
PATINDEX('A',[Middle Name]) + 
PATINDEX('Mak',[Middle Name]) + 
PATINDEX('Street',[Address1]) + 
PATINDEX('Road',[Address1]) + 
PATINDEX('UAE',[Address1]) + 
PATINDEX('Street',[Address2]) + 
PATINDEX('Road',[Address2]) + 
PATINDEX('UAE',[Address2]) > 0

注意DelimitedSplit8k的使用，您需要在实例上使用它才能完成这一任务。

编辑/注意:这不是注入安全。特别是因为+ DSc.Item +__。在它们的示例数据中，OP提供了已经引用的字符串；因此，假定字符串是正确引用的；例如，而不是 "[" & ColumnName & "]" (仍然对注入开放，因为通过的任何]都不会转义)。如果列名在其他地方没有正确引用，我强烈建议删除传递值中的方括号([])，然后使用+ QUOTENAME(DSc.Item) +。