how to convert unicode to german umlauts with sql query SQL Server-CodePudding

I have a SQL table that stores values entered by the user. After users entered German umlauts, it was saved in SQL as follows: müssen, Üben and much more. Now I need a SQL Query that includes all columns ü replaced with ü.

Can someone help me with the SQL query?

CodePudding user response：

I think this could be your solution:

UPDATE db.tablename 
SET fieldname = REPLACE(fieldname, '&#252;', 'ü')
WHERE fieldname LIKE '%&#252;%';

CodePudding user response：

This is taken from lptr's comment, which was a link to a DB Fiddle. I have posted this an answer to preserve it's usefulness, as comments can be deleted at any time.

Here they use makes use of the xml data type to implicitly convert back the value to what it should be:

select *, cast(t.col as xml).value('.', 'nvarchar(50)')
from
(
values(N'm&#252;ssen'), (N'&#220;ben')
) as t(col);

db<>fiddle

You may, however, find better performance using (./text())[1] instead of just ..