Forum Discussion
BUG in REPLACE FUNCTION?
Hi, I have a very weird issue. We have a requirement to get rid of special Euro language characters. So below replaces Þ with T. However, random letters like H are affected. The below gives GAT as a...
Hi, Panos.
This is expected behaviour and is related to character set (or collation) translation.
Here's some examples that highlight the issue as well as making use of COLLATE to perform a binary comparison instead, which avoids the issue.
SELECT REPLACE(N'GATH', N'Þ', N'P') , REPLACE(N'GATH' COLLATE Latin1_General_BIN, N'Þ', N'P') , REPLACE(N'GAÞ' COLLATE Latin1_General_BIN, N'Þ', N'P')Output:
- The first test reproduces the problem;
- The second test demonstrates that nothing is replaced, as is expected
- The third test demonstrates that only the specified character is replaced, as is expected.
Cheers,
Lain
