CODEWITHSUNDEEP
sql-servert-sql
Simon Wiggins
Simon Wiggins

Reputation: 71

Is it possible to construct this sql update query without wrapping it in a cursor?

I have 2 tables. One containing key/value pairs. The other containing varchar columns that need all instances of the keys within the text to be replaced with corresponding values.

I have implemented this in a stored procedure using a cursor which works. But I do need the stored procedure to perform as efficiently as possible. My first thought is to remove the cursor. Is that possible? Perhaps using a recursive cte? Example as follows.

Setup

DECLARE @Dictionary TABLE 
(
    WordKey NVARCHAR(255),
    WordValue NVARCHAR(4000)
)

INSERT INTO @Dictionary
VALUES
    ('[key1]', 'Value1'),
    ('[key2]', 'Value2'),
    ('[key3]', 'Value3')


DECLARE @Phrasings TABLE
(
    Phrasing1 NVARCHAR(4000),
    Phrasing2 NVARCHAR(4000),
    Phrasing3 NVARCHAR(4000)
)

INSERT INTO @Phrasings
VALUES
    ('[key1]','random text','random text'),
    ('random text','random [key2] text','random text'),
    ('random text [key1]','random [key2] text','random [key1] [key3] text')

Main Query

DECLARE @WordKey NVARCHAR(255)
DECLARE @WordValue NVARCHAR(max)

DECLARE parsing_cursor CURSOR FOR
SELECT WordKey,WordValue FROM @Dictionary

OPEN parsing_cursor

FETCH NEXT FROM parsing_cursor
INTO @WordKey, @WordValue

WHILE @@FETCH_STATUS = 0 
    BEGIN

    UPDATE @Phrasings SET
        Phrasing1 = REPLACE(Phrasing1, @WordKey, @WordValue),
        Phrasing2 = REPLACE(Phrasing2, @WordKey, @WordValue),
        Phrasing3 = REPLACE(Phrasing3, @WordKey, @WordValue)

    FETCH NEXT FROM parsing_cursor
    INTO @WordKey, @WordValue
    END

CLOSE parsing_cursor
DEALLOCATE parsing_cursor

Expected Result

Phrasing1               Phrasing2               Phrasing3
^^^^^^^^^               ^^^^^^^^^               ^^^^^^^^^
"Value1"                "random text"           "random text"
"random text"           "random Value2 text"    "random text"
"random text Value1"    "random Value2 text"    "random Value1 Value3 text"

Upvotes: 2

Views: 150

Answers (2)

bummi
bummi

Reputation: 27385

With a persistant table Dictionary you could use a user function like this

Create Function F_Replace(@Phrasing NVARCHAR(4000)) RETURNS NVARCHAR(4000)
begin
  Declare @Result NVARCHAR(4000)
  Select @Result=@Phrasing
  Select @Result= REPLACE(@Result,WordKey,WordValue)
  from Dictionary
  Return @Result
end

you could have a query like

Select dbo.F_Replace(Phrasing1) ,dbo.F_Replace(Phrasing2)    ,dbo.F_Replace(Phrasing3)       
from @Phrasings

Upvotes: 1

Devart
Devart

Reputation: 122032

Try this one -

Query:

DECLARE @Dictionary TABLE (WordKey VARCHAR(255), WordValue VARCHAR(400))
INSERT INTO @Dictionary (WordKey, WordValue)
VALUES
    ('[key1]', 'Value1'),
    ('[key2]', 'Value2'),
    ('[key3]', 'Value3')

DECLARE @Phrasings TABLE (Phrasing1 VARCHAR(400), Phrasing2 VARCHAR(400), Phrasing3 VARCHAR(400))
INSERT INTO @Phrasings (Phrasing1, Phrasing2, Phrasing3)
VALUES
    ('[key1]', 'random text', 'random text'),
    ('random text', 'random [key2] text', 'random text'),
    ('random text [key1]', 'random [key2] text', 'random [key1] [key3] text')

;WITH cte AS 
(
    SELECT x = (
        SELECT  
              Phrasing1
            , Phrasing2
            , Phrasing3
        FROM @Phrasings t
        FOR XML PATH('ID')
    ), lvl = 1

    UNION ALL

    SELECT REPLACE(x, t.WordKey, t.WordValue), lvl + 1
    FROM cte
    JOIN (
        SELECT WordKey, WordValue, rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
        FROM @Dictionary
    ) t ON rn = lvl
)
SELECT 
      Phrasing1 = t.c.value('Phrasing1[1]', 'VARCHAR(400)')
    , Phrasing2 = t.c.value('Phrasing2[1]', 'VARCHAR(400)')
    , Phrasing3 = t.c.value('Phrasing3[1]', 'VARCHAR(400)')
FROM (
    SELECT TOP 1 x = CAST(x AS XML)
    FROM cte
    ORDER BY lvl DESC
) r
CROSS APPLY x.nodes('/ID') t(c)

Results:

Phrasing1            Phrasing2            Phrasing3
-------------------- -------------------- ---------------------------
Value1               random text          random text
random text          random Value2 text   random text
random text Value1   random Value2 text   random Value1 Value3 text

Upvotes: 1

Related Questions