none
How to merge multiple update in one RRS feed

  • Question

  • Dear all,

    I need to update a Table field based on multiple criteria a. Actually my update querry is as below :

    UPDATE BODYCONTENT 
    SET BODY =CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'é','é') AS NText)

    But what I need is that during this update of BODY field I need to execute also the following

    SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'é','é') AS NText)
       SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'è','è') AS NText)
       SET BODY= CAST(REPLACE(CAST(BODY as nvarchar(MAX)), 'à', 'à')AS NText);
       SET BODY= CAST(REPLACE(CAST(BODY as nvarchar(MAX)), '’', '''')AS NText);
    

    How can I merge the update within a single SET BODY= ?

    Any idea ?

    Thanks for help

    regards

    Friday, September 13, 2019 6:30 AM

Answers

  • By cascading the REPLACE function like:

    SET BODY = REPLACE(REPLACE(REPLACE(REPLACE(CAST(BODY as nvarchar(MAX))
                                              ,'é','é')
                                      ,'è','è')
                              , 'à', 'à')
                     , '’', '''')


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by cc_______ Friday, September 13, 2019 7:29 AM
    • Marked as answer by wakefun Tuesday, September 17, 2019 7:02 AM
    Friday, September 13, 2019 6:47 AM

All replies

  • By cascading the REPLACE function like:

    SET BODY = REPLACE(REPLACE(REPLACE(REPLACE(CAST(BODY as nvarchar(MAX))
                                              ,'é','é')
                                      ,'è','è')
                              , 'à', 'à')
                     , '’', '''')


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by cc_______ Friday, September 13, 2019 7:29 AM
    • Marked as answer by wakefun Tuesday, September 17, 2019 7:02 AM
    Friday, September 13, 2019 6:47 AM
  • Try this too:

     

    declare @b as nvarchar(MAX)

    update BODYCONTENT

    set @b = BODY,

        @b = REPLACE(@b, N'é', N'é'),

        @b = REPLACE(@b, N'è', N'é'),

        . . . ,

        BODY = @b


    Friday, September 13, 2019 6:47 AM
  • Thnaks Olaf for your reply,

    but in your cascading approach where should I place the as Cast as NText which I need to be there

    thanks for help

    regards

    Friday, September 13, 2019 7:01 AM
  • I place the as Cast as NText which I need to be there

    You can use one CAST on the outer side of all replaces. Why do you Need that cast? Note: The data type NTEXT is deprecated since SQL Server 2005 and shouldn't be used; better use nvarchar(max) as data type.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, September 13, 2019 7:20 AM
  • Hi,

    You can also use 'update' to avoid repeatedly using 'set body'. Here's the example you can refer to: 

    use exercising
    
    /*second column is the value you want to replace, third column is the value you want to replace to*/
    IF OBJECT_ID('test') IS NOT NULL drop table test
    create table test( Body varchar(30), replacevalue1 varchar(30),replacevalue2 varchar(30))
    go
    insert into test values
    ('afdadéee','é','é'),
    ('dafefeeè123','è','è'),
    ('ddfefà','à','à'),
    ('’ddd','’','''')
    
    /* this is the table you want to update*/
    IF OBJECT_ID('BODYCONTENT') IS NOT NULL drop table BODYCONTENT
    create table BODYCONTENT( Body varchar(30))
    go
    insert into BODYCONTENT values
    ('afdadéee'),
    ('dafefeeè123'),
    ('ddfefà'),
    ('’ddd')
    
    
    update t set BODY =replace(CAST(t.BODY as nvarchar(MAX)),t1.replacevalue1,t1.replacevalue2)
    from BODYCONTENT t
    join test t1
    on t.body=t1.body
    
    select * from BODYCONTENT
    /*
    
    Body
    ------------------------------
    afdadéee
    dafefeeè123
    ddfefà
    ’ddd/*

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 13, 2019 7:40 AM