Replacing string values in SQL

Question

I'm trying to change some string values in SQL using while loops and replace function. I have two tables from where I get the values and formulas that I gotta change. The main problem is that when I execute the code, I can change the first matching pattern of the very first formula but the other patterns don't change. I don't know if the problem is the way that I set the counters or the "Selects" used for searching values. My code:

IF OBJECT_ID('tempdb..#TABLA_TEMP') IS NOT NULL BEGIN
    DROP TABLE #TABLA_TEMP
END
IF OBJECT_ID('tempdb..#TABLE_VALUES') IS NOT NULL BEGIN
    DROP TABLE #TABLE_VALUES
END

CREATE TABLE #TABLA_TEMP (
    ID INT IDENTITY NOT NULL PRIMARY KEY, 
    NUMERO VARCHAR(max)
)

INSERT INTO #TABLA_TEMP(NUMERO)VALUES('X=''A01TGR.420.JHNB''+''A02TGR.421.ZASD''')
INSERT INTO #TABLA_TEMP(NUMERO)VALUES('X=''A08TGR.427.YUJK''+''A03TGR.422.CVBN''')
INSERT INTO #TABLA_TEMP(NUMERO)VALUES('X=''A04TGR.423.TYUI''+''A05TGR.424.QWER''')

CREATE TABLE #TABLE_VALUES (
    ID INT IDENTITY NOT NULL PRIMARY KEY, 
    ID_INDICADOR INT, 
    CODIGO VARCHAR(max), 
    MtoValor DECIMAL(18,2)
)

INSERT INTO #TABLE_VALUES(ID_INDICADOR, CODIGO, MtoValor) VALUES (19520, 'A01TGR.420.JHNB', 50.00)
INSERT INTO #TABLE_VALUES(ID_INDICADOR, CODIGO, MtoValor) VALUES (19521, 'A02TGR.421.ZASD', 25.00)
INSERT INTO #TABLE_VALUES(ID_INDICADOR, CODIGO, MtoValor) VALUES (19522, 'A03TGR.422.CVBN', 15.00)
INSERT INTO #TABLE_VALUES(ID_INDICADOR, CODIGO, MtoValor) VALUES (19523, 'A04TGR.423.TYUI', 65.00)
INSERT INTO #TABLE_VALUES(ID_INDICADOR, CODIGO, MtoValor) VALUES (19524, 'A05TGR.424.QWER', 30.00)
INSERT INTO #TABLE_VALUES(ID_INDICADOR, CODIGO, MtoValor) VALUES (19527, 'A08TGR.427.YUJK', 45.00)

select * from #TABLA_TEMP
--select * from #TABLE_VALUES

DECLARE @counter int
SET @counter = 1
DECLARE @counterInterno int
SET @counterInterno = 1

WHILE(@counter <= (SELECT MAX(ID) FROM #TABLA_TEMP)) BEGIN  
    DECLARE @MatchExpression VARCHAR(max)
    SET @MatchExpression = (''+ CAST(((SELECT CODIGO FROM #TABLE_VALUES
                                WHERE ID = @counter)) AS VARCHAR) +'')

    --WHILE 1 = 1
    WHILE(@counterInterno <= (SELECT MAX(ID) FROM #TABLE_VALUES)) BEGIN
        DECLARE @valorInterno VARCHAR(MAX)
        SET @valorInterno = (''+ CAST(((SELECT MtoValor FROM #TABLE_VALUES V
                                WHERE V.ID = @counterInterno)) AS VARCHAR) +'')

        DECLARE @RetVal varchar(max)
        SET @RetVal =  (SELECT REPLACE(NUMERO, @MatchExpression, @valorInterno)
                        FROM #TABLA_TEMP T WHERE T.ID = @counterInterno)

        IF(@RetVal IS NOT NULL)     
            UPDATE #TABLA_TEMP 
            SET NUMERO = @RetVal
            WHERE ID = @counter
        ELSE
            break

        SET @counterInterno = @counterInterno + 1
        SET @counter = @counter + 1
    END
END

SELECT * FROM #TABLA_TEMP

At the end, the idea is to get something like this:

X='65'+'15'+'25'

Any help would be appreciated.

shA.t · Accepted Answer · 2015-06-07 04:04:08Z

3

Try this:

SELECT ROW_NUMBER() OVER (ORDER BY t1.ID, t2.ID) As RowNo, t1.ID, t1.NUMERO, t2.CODIGO, t2.MtoValor 
INTO #NewTemp
FROM #TABLA_TEMP t1 JOIN #TABLE_VALUES t2 ON t1.NUMERO LIKE '%' + t2.CODIGO + '%'

DECLARE @Counter int = 1, @ID as int, @Codigo varchar(max), @MtoValor decimal(18, 2)

WHILE (EXISTS (SELECT 1 FROM #NewTemp WHERE RowNo = @Counter))
BEGIN

    SELECT @ID = nt.ID, @Codigo = nt.CODIGO, @MtoValor = nt.MtoValor
    FROM #NewTemp nt
    WHERE nt.RowNo = @Counter

    UPDATE #TABLA_TEMP 
    SET NUMERO = REPLACE(NUMERO, @Codigo, CAST(@MtoValor AS varchar(25)))
    WHERE ID = @ID

    SET @Counter = @Counter + 1
END

SELECT *
FROM #TABLA_TEMP

For this:

ID  | NUMERO
----+-----------------------
1   | X='50.00'+'25.00'
2   | X='45.00'+'15.00'
3   | X='65.00'+'30.00'

answered Jun 7, 2015 at 4:04

shA.t

17k5 gold badges59 silver badges121 bronze badges

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

1 Comment

Adrian87 Over a year ago

Wow bro, it worked like a charm !!!!! The way you just use one while loop makes it a very elegant and efficient solution ! Thanks.

Collectives™ on Stack Overflow

Replacing string values in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related