Replacing column from query via patterns

Question

How to replace all column in the string

DECLARE @invalidColumns varchar(200) = 'abc, xyz'

DECLARE @sqltext varchar(max) = '((abc = ''sometext'') OR (xyz    = '' some more text'')) OR
                                 (pqr = ''vb'') AND ( abc != ''  text '')
                                 OR ((hht = ''asd asd'') AND ( xyz =   ''  More text  '' ))
'

in above example I have to search for column abc and xyz in @sqlText and replace them all with 1=1

so the final output should look like

((1 = 1) OR (1    = 1)) OR
                             (pqr = ''vb'') AND ( 1 != 1)
                             OR ((hht = ''asd asd'') AND ( 1 =  1 ))

I tried to do in this way but it seems like replace function is not working with patterns, also it will do for only first finding. There may be possibility that column abc and xyz may appear multiple times in a statement.

SELECT CASE WHEN PATINDEX('%abc% ''%',@sqltext) > 0
            THEN REPLACE (@sqlText,'%abc% ''%', '1=1')
            END

Any help

Edit

I tried this, but it is not giving me proper result

DECLARE @len INT
DECLARE @initializor INT = 1
DECLARE @First INT
DECLARE @Result VARCHAR(max) SET @Result = ''
DECLARE @EndPattern INT
SET @len = LEN(@sqltext)


WHILE( @initializor <= @len)
BEGIN
        SET @First = PATINDEX('%abc% ''%', SUBSTRING(@sqltext, @initializor, @Len))
         SET @EndPattern = 1
              WHILE PATINDEX('%abc% ''%', SUBSTRING(@sqltext, @initializor, @EndPattern)) = 0
            SET @EndPattern = @EndPattern + 1

        IF COALESCE(@First, 0) <> 0
        BEGIN
             SET @Result = @Result + SUBSTRING(@sqltext, @initializor, @First - 1)
             SET @initializor = @initializor + @First - 1

             SET @EndPattern = 1
              WHILE PATINDEX('%abc% ''%', SUBSTRING(@sqltext, @initializor, @EndPattern)) = 0
            SET @EndPattern = @EndPattern + 1
         -- Find end of pattern range
         WHILE PATINDEX('%abc% ''%', SUBSTRING(@sqltext, @initializor, @EndPattern)) > 0
               AND @Len >= (@initializor + @EndPattern - 1)
                    SET @EndPattern = @EndPattern + 1

         --Either at the end of the pattern or @Next + @EndPattern = @Len
         SET @Result = @Result + '''1=1'''
         SET @initializor = @initializor + @EndPattern - 1


        END


        SET @initializor = @initializor + 1;
END


SELECT @Result

score 1 · Accepted Answer · answered Oct 06 '13 at 13:43

1

Introduce some more information about the replace text. Here is a great example:

DECLARE @invalidColumns varchar(max) = replace('abc, xyz', ' ', '')

DECLARE @sqltext varchar(max) = ''

;WITH t1 as
(
SELECT '(*'    pat, '(abc = ''sometext'')' txt, 'abc' col
UNION ALL SELECT 'OR *)' pat, 'OR (xyz = '' some more text'')' txt, 'xyz' col
UNION ALL SELECT 'OR *'  pat, '(pqr = ''vb'')' txt, 'pqr' col
UNION ALL SELECT 'AND *' pat, 'abc != ''  text ''' txt, 'abc' col
UNION ALL SELECT 'OR (*' pat, '(hht = ''asd asd'')' txt, 'hht' col
UNION ALL SELECT 'AND(*)' pat, 'xyz =   ''''''' txt, 'xyz' col
), t2 as
(
SELECT t.c.value('.', 'VARCHAR(20)') col
FROM (
SELECT x = CAST('<t>' + 
    REPLACE(@invalidColumns, ',', '</t><t>') + '</t>' AS XML)
) a
CROSS APPLY x.nodes('/t') t(c)
) 
SELECT @sqltext = @sqltext 
+ replace(t1.pat, '*', case when t2.col is null then t1.txt else ' 1 = 1 ' end)
FROM t1
LEFT JOIN t2 on t1.col = t2.col

SELECT @sqltext

answered Oct 06 '13 at 13:43

t-clausen.dk

43,517
12
59
92

+1 for the efforts but this seems to be a manual way to replace columns. I have around 100+ queries whose conditions may change daily. I can not hard code them :( – Zerotoinfinity Oct 06 '13 at 15:12
@Zerotoinfinite you can still use my method, just create a function to split the sqltext and return a table with the same 3 columns. Going to show you later. I will look into it later – t-clausen.dk Oct 06 '13 at 15:17
Did you get a chance to look into this.. I tried to do it and messed up the code :( – Zerotoinfinity Oct 07 '13 at 11:12
@Zerotoinfinite hello, I have been ill until now. I have looked at your code. I would be able to solve it. But the solution would be risky conserning searching in the text. I would never make a solution for any production system using this process. So rather than provide you with spaghetti code, I urge you to find another solution – t-clausen.dk Oct 14 '13 at 12:12
I did it by using REPLACE, PATINDEX, STUFF and CHARINDEX. As that is on my client server I can not paste the query here. But if you want I can write the logic. I sincerely thankful to you for your assistance. :) – Zerotoinfinity Oct 15 '13 at 10:47
@Zerotoinfinite I already wrote the recursive syntax to split it up (and erased it again). Just wanted to encourage you to use a better solution. Too many things can go wrong – t-clausen.dk Oct 15 '13 at 10:57

Replacing column from query via patterns

1 Answers1