Update using select in sql server

Transact-SQL
1

I have a table User with UserName and Id.

This table can have special characters in the Username field:

Id UserName

1 Ær

2 År

And multiple other characters

I need to store these characters in their UTF-8 encoded format.

I created another table with the special character and its UTF-8 format.

SpecialChar UTFChar

 Â

à Ã

Ä Ã„

Å Ã…

Æ Ã†

Now i want to write a query to update the table User with the replacement UTFChar for SpecialChar found in the UserName

something like:

update User set UserName = Replace(UserName, SpecialChar, UTFChar)

but what do i join these tables on.

Please help on this.

2 
UPDATE u SET
	u.UserName = REPLACE(u.Username, u.SpecialChar, mUTFChar)
FROM
	UsernameTable AS u
	INNER JOIN MappingTable AS m ON
		m.SpecialChar = u.SpecialChar;
3

u.SpecialChar doesnt exist as such. It is a data field. Something like år, ølga, ægis will be its content.
I am stuck at this.

4 
...
INNER JOIN MappingTable AS m ON
		u.Username = '%' + m.SpecialChar + '%'
5

It doesnt work with inner join.
I tried using left outer join, but it returns NULL for all the corresponding records.

select
u.UserName , REPLACE(u.UserName, m.SpecialChar, m.UTFChar)
FROM
User AS u
left JOIN MappingTable AS m ON
u.UserName = '%' + m.SpecialChar + '%';

if i put
u.UserName like '%' + m.SpecialChar + '%';
still i get multiple records.

6

Try this example. You can copy the code to an SSMS query window and run it.

IF OBJECT_ID('tempdb..#UserTable') IS NOT NULL DROP TABLE #UserTable;
IF OBJECT_ID('tempdb..#Mapping') IS not NULL DROP TABLE #Mapping;

CREATE TABLE #UserTable(Id INT, Username NVARCHAR(32));
INSERT INTO #UserTable  VALUES
(1,N'123Â123'), (2,N'Ãr1234'), (3,N'123Æ'), (4,N'123ÂÆÃ123');

CREATE TABLE #Mapping(SpecialChar NVARCHAR(32), UTFChar NVARCHAR(32));
INSERT INTO #Mapping VALUES 
(N'Â',N'X'),(N'Ã',N'Y'),(N'Æ',N'Z'),(N'Å',N'W')

UPDATE u SET u.UserName = REPLACE(u.Username, m.SpecialChar, m.UTFChar) 
FROM
	#UserTable AS u
	INNER JOIN #Mapping AS m ON
		u.Username LIKE  N'%' + m.SpecialChar + N'%';

SELECT * FROM #UserTable;

As you will observe from the last row in the Username table, you will have to run the update statement multiple times to get all the characters replaced.