CODEWITHSUNDEEP
sql-serversql-server-2008-r2
MAK
MAK

Reputation: 7260

Generate dynamic PATINDEX statement

I have the following two strings:

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

Note: Both strings are dynamic may comes with more or less values.

Expected result: Want to if any of the given text present in the given columns using the PATINDEX. The following PATINDEX statement gonna used in the WHERE clause of the SELECT statement.

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

My try:

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);

But unable to get the expected result.

Upvotes: 2

Views: 254

Answers (1)

Thom A
Thom A

Reputation: 95561

This is not pretty dynamic SQL, however...

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;

This outputs:

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

Note the use of DelimitedSplit8k, which you'll need on your instance to get this done.

Edit/Note: This is not injection safe. Specifically because of + DSc.Item +. The OP, in their sample data, provides already quoted strings; it is therefore assumed that the strings are properly quoted; I.e. not "[" & ColumnName & "]" (that is still open to injection, as any ] passed won't be escaped). If the columnnames are not properly quoted elsewhere, I strongly suggest removing the brackets ([]) in the passed value and using + QUOTENAME(DSc.Item) + instead.

Upvotes: 2

Related Questions