SQLite how to replace characters while excluding some characters-CodePudding

I have an SQLite file containing words and their phonetic transcription as follows:

Source (sɔɹs)
Song (sɔŋ)
Daughter (ˈdɔtəɹ)
...

I want to change every 'ɔ' character with 'a' character if 'ɔ' isn't followed by "ŋ" or "ɹ"

My code so far:

UPDATE words
SET phonetictranscription_ame = replace(phonetictranscription_ame, "ɔ", "a")
WHERE phonetictranscription_ame NOT IN ("ɔŋ", "ɔɹ")

This code replaces the 'ɔ' character with 'a' character but it also replaces the 'ɔ's with words including "ɔŋ" and "ɔɹ". Is something wrong with my code?

CodePudding user response：

First, replace all occurrences of 'ɔŋ' and 'ɔɹ' with symbols that you are sure do not exist in the column (I chose '|1' and '|2' but I'm not a linguist, so you can change them).

Then replace all 'ɔ's to 'a's.

Finally restore back the 'ɔŋ's and 'ɔɹ's:

UPDATE words
SET phonetictranscription_ame = 
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            phonetictranscription_ame, 'ɔŋ', '|1'
          ), 'ɔɹ', '|2'
        ), 'ɔ', 'a'
      ), '|1', 'ɔŋ'
    ), '|2', 'ɔɹ'
  )
WHERE phonetictranscription_ame LIKE '%ɔ%';

See the demo.