Problem when replacing greater and smaller signs in SQL Server

user_8660210 0
Viewed 92

maybe someone of you can help me with the following question. I try to translate greater and smaller signs in SQL Server to the specific Unicode Signs 706 (<) and 707 (>).

When I replace "<>" then the result is correct (HEX: C202 C302 )

But when I try to replace "><" then the result is unexpected: (HEX: 3E00 C202)

Maybe you want to try it:

CREATE TABLE Test (Id int, MyText nvarchar(255))

insert into Test (Id, MyText) values  (1, '<>');
insert into Test (Id, MyText) values  (2, '><');

SELECT id, MyText, convert(varbinary, MyText) FROM Test    

-- 1  <>  0x 3C00 3E00         
-- 2  ><  0x 3E00 3C00   

UPDATE Test SET MyText = REPLACE(MyText, '<', NCHAR(706)) 
UPDATE Test SET MyText = REPLACE(MyText, '>', NCHAR(707)) 

SELECT id, MyText, convert(varbinary, MyText) FROM Test  

-- 1  ˂˃  0x C202 C302   (correct)
-- 2  >˂  0x 3E00 C202   (!!! Why is the greater sign not replaced to C302 ???)

Thank you for your help. Robert

unicode sql-server
1 Answers

I can't answer why this isn't working with REPLACE, however, if you use TRANSLATE the problem does not persist:

UPDATE dbo.Test SET MyText = TRANSLATE(MyText, '<>', NCHAR(706)+NCHAR(707));

db<>fiddle

Alternatively, if you COLLATE the column to a binary collation inside REPLACE, the replacement works too.

UPDATE dbo.Test SET MyText = REPLACE(MyText COLLATE Latin1_General_BIN, N'>', NCHAR(707));
UPDATE dbo.Test SET MyText = REPLACE(MyText COLLATE Latin1_General_BIN, N'<', NCHAR(706));

db<>fiddle

user_20299830
Related