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;
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.
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.