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

asked 11 Nov, 15:38

Pirre's gravatar image

Pirre
313
accept rate: 0%


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.

permanent link

answered 11 Nov, 16:16

John%20Smirnios's gravatar image

John Smirnios
11.4k394154
accept rate: 38%

Thanks, byte_replace was what I needed.

(11 Nov, 16:43) Pirre
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×4

question asked: 11 Nov, 15:38

question was seen: 88 times

last updated: 11 Nov, 16:43