I want to replace 'Σ' with 'ä' and 'σ' with 'å'. But both of these replace "Σ" and "σ" in their respective replace. What am I doing wrong? Select 'σterb rΣttskydd' OriginalText, replace(OriginalText, 'Σ','ä') A1, replace(OriginalText, 'σ', 'å') A2 |
The collation you are using is probably case-insensitive therefore Σ is indistinguishable from σ at least within the CHAR domain. You could use byte_replace (or cast the input parameters to binary) to do the replacement on a byte basis rather than a character basis but it won't always be valid when using certain character sets. For example, SJIS allows '\' as a follow byte of a multibyte character and a binary replace would match a '\' against a follow byte. Similarly, a valid SJIS character could match the follow byte of one character and the lead of the next. For example, \x81\x82\x83\x84 is a valid sequence of two SJIS characters (\x81\x82 and \x83\x84). However, \x82\83 is also a valid SJIS character. If you search for \x82\x83 via a binary replace, you will find a match and you don't want that. Thanks, byte_replace was what I needed.
(11 Nov '22, 16:43)
Pirre
|
Hello this is Gulshan Negi Well, to replace "Σ" with "ä" and "σ" with "å" separately, you should use a case-insensitive replace function or method that can distinguish between "Σ" and "σ". You can achieve this by using regular expressions with the "i" flag to make the replace function case-insensitive, like in the example provided. I hope it will help. Thanks |