How can I replace the Sigma symbol with replace function?

[former_SQLA_member1695013]

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

[johnsmirnios · Accepted solution]

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.

[former_SQLA_member1695013 · Accepted solution]

Thanks, byte_replace was what I needed.

[former_SQLA_member1694947]

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