CODEWITHSUNDEEP
sqlsql-server-2005
Torpedo
Torpedo

Reputation: 139

Multiple replacements in string in single Update Statement in SQL server 2005

I've a table 'tblRandomString' with following data:

ID  ItemValue

1   *Test"
2   ?Test*

I've another table 'tblSearchCharReplacement' with following data

Original   Replacement

*          `star`
?          `quest`
"          `quot`
;          `semi`

Now, I want to make a replacement in the ItemValues using these replacement. I tried this:

Update T1
SET ItemValue = select REPLACE(ItemValue,[Original],[Replacement])
FROM dbo.tblRandomString T1
JOIN
dbo.tblSpecialCharReplacement T2
ON T2.Original IN ('"',';','*','?')

But it doesnt help me because only one replacement is done per update.

One solution is I've to use as a CTE to perform multiple replacements if they exist.

Is there a simpler way?

Upvotes: 1

Views: 2778

Answers (2)

Derek Tomes
Derek Tomes

Reputation: 4007

Will skipping the join table and nesting REPLACE functions work? Or do you need to actually get the data from the other table?

-- perform 4 replaces in a single update statement
UPDATE T1
SET ItemValue = REPLACE(
                  REPLACE(
                    REPLACE(
                      REPLACE(
                        ItemValue,'*','star')
                      ItemValue,'?','quest')
                    ItemValue,'"','quot')
                  ItemValue,';','semi')

Note: I'm not sure if you need to escape any of the characters you're replacing

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

Sample data:

declare @RandomString table (ID int not null,ItemValue varchar(500) not null)
insert into @RandomString(ID,ItemValue) values
(1,'*Test"'),
(2,'?Test*')

declare @SearchCharReplacement table (Original varchar(500) not null,Replacement varchar(500) not null)
insert into @SearchCharReplacement(Original,Replacement) values
('*','`star`'),
('?','`quest`'),
('"','`quot`'),
(';','`semi`')

And the UPDATE:

;With Replacements as (
    select
        ID,ItemValue,0 as RepCount
    from
        @RandomString
    union all
    select
        ID,SUBSTRING(REPLACE(ItemValue,Original,Replacement),1,500),rs.RepCount+1
    from
        Replacements rs
            inner join
        @SearchCharReplacement scr
            on
                CHARINDEX(scr.Original,rs.ItemValue) > 0
), FinalReplacements as (
    select
        ID,ItemValue,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RepCount desc) as rn
    from
        Replacements
)
update rs
    set ItemValue = fr.ItemValue
from
    @RandomString rs
        inner join
    FinalReplacements fr
        on
            rs.ID = fr.ID and
            rn = 1

Which produces:

select * from @RandomString

ID          ItemValue
----------- -----------------------
1           `star`Test`quot`
2           `quest`Test`star`

What this does is it starts with the unaltered texts (the top select in Replacements), then it attempts to apply any valid replacements (the second select in Replacements). What it will do is to continue applying this second select, based on any results it produces, until no new rows are produced. This is called a Recursive Common Table Expression (CTE).

We then use a second CTE (a non-recursive one this time) FinalReplacements to number all of the rows produced by the first CTE, assigning lower row numbers to rows which were produced last. Logically, these are the rows which were the result of applying the last applicable transform, and so will no longer contain any of the original characters to be replaced. So we can use the row number 1 to perform the update back against the original table.

This query does do more work than strictly necessary - for small numbers of rows of replacement characters, it's not likely to be too inefficient. We could clear it up by defining a single order in which to apply the replacements.

Upvotes: 2

Related Questions