SQL usage variable in REPLACE function

Question

DECLARE
    @vLiczba int = 0,
    @vWynik varchar (30),
    @min int;

BEGIN
    WHILE @vLiczba < 1
    BEGIN
        SET @min = (SELECT MIN(RIGHT(LEFT(query_transaction, 36), 4))
                    FROM testowa_tabela1 
                    WHERE query_transaction LIKE 'update%');
        PRINT 'min value: ' + CAST (@min as varchar(10)) ;
        
        UPDATE testowa_tabela1
        SET query_transaction = REPLACE(query_transaction, '@min', '9999') 

        SET @vLiczba = @vLiczba + 1;
    END;

    PRINT 'Verified record: ' + CAST (@vLiczba as varchar(10)) + ', min value: ' + CAST (@min as varchar(10)) ;
END;

In row 14 in REPLACE(query_transaction, '@min', '9999'), @min is not treated as variable.

Can you help me fix my problem? REPLACE statement is executed in the loop as I checked but without result.

marc_s · Accepted Answer · 2021-09-19 09:53:16Z

2

You just need to get rid of the single quotes around the @min. When you use single quotes, The variable is treated as a string, not as a variable. Also, you need a semi-colon after your updated statement. So your updated code would be -

DECLARE
    @vLiczba int = 0,
    @vWynik varchar (30),
    @min int;
BEGIN
    WHILE @vLiczba < 1
    BEGIN
       SET @min = (select min(right(left(query_transaction,36),4))
                     from testowa_tabela1
                    where query_transaction like 'update%');
     PRINT 'min value: ' + CAST (@min as varchar(10));
        
    UPDATE testowa_tabela1
       SET query_transaction = REPLACE( query_transaction,@min,'9999');
       SET @vLiczba = @vLiczba + 1;
    END;
    PRINT 'Verified record: ' + CAST (@vLiczba as varchar(10)) + ', min value: ' + CAST (@min as varchar(10)) ;
END;

edited Sep 19, 2021 at 9:53

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 3, 2021 at 10:51

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL usage variable in REPLACE function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related