Tell me how to replace the characters in the database table MS SQL. There is a column of type ntext , in which you need to find all the letters "a" and replace with "b".

And so that the replacement was made in UTF-8 encoding ...

    2 answers 2

    If the version supports varchar (max) type:

     update t set f = replace(cast(f as nvarchar(max)), N'а', N'б')
    • And so that the replacement was made in UTF-8 encoding ... how? - Dlevin
    • So try: update t set f = replace (cast (f as nvarchar (max)), N'a ', N'b') - msi
    • Characters "a" are included in the words will be replaced? Something changes the entire entry in the Cyrillic cell to ??????? ???? ????? .... Encoding of a field with text in a table SQL_Latin1_General_CP1_CI_AS - Dlevin
    • Characters will be replaced. And the collation must be made kirrilicheskuyu. Latin1 does not contain Cyrillic, therefore you see ??????? ???? ?????. Or use unicode. Here is the test: ------ declare @t table (f ntext, f2 ntext); insert into @t values ​​(N'aaaaaannnaaaaaaaammm ',' aaaaaannnaaaaaaammmm '); update @t set f = replace (cast (f as nvarchar (max)), N'a ', N'b'), f2 = replace (cast (f2 as nvarchar (max)), N'a ', N' b '); select * from @t ----- Compare 2 columns - msi
    • And how to make this query replace the characters in the SQL_Latin1_General_CP1_CI_AS coding; update t set f = replace (cast (f as varchar (max)), 'a', 'b'); just the coding in the table cannot be changed. But it is necessary that the Cyrillic alphabet is recorded correctly in the table - Dlevin
     UPDATE table SET field = REPLACE(field, "а", "б")
    • one
      Two errors: 1. REPLACE does not work for type ntext. 2. "a" -> 'a' - msi