[FIXED] How to write the exact text next to the text and replace certain letters in it? SQLite

Issue

I am creating a phonetic dictionary for American English. Here are some examples:

vocabulary      phonetic_transcription
dream           dɹim
drain           dɹeɪn
drink           dɹɪŋk
adrenaline      əˈdɹɛnəlɪn

If dɹ is in the phonetic_transcription, I want to write an alternative to it replacing 'd' with 'ʤ'. The expected outcome is:

vocabulary      phonetic_transcription
dream           dɹim / ʤɹim
drain           dɹeɪn / ʤɹeɪn
drink           dɹɪŋk / ʤɹɪŋk
adrenaline      əˈdɹɛnəlɪn / əˈʤɹɛnəlɪn

I know how to change a certain element with another with replace function but I have no idea how to do it like above.

Solution

Use string functions like INSTR() to filter the table so that only rows with phonetic_transcription that contain 'dɹ' will be updated and REPLACE() to replace the string 'dɹ' with 'ʤɹ':

UPDATE tablename
SET phonetic_transcription = phonetic_transcription || ' / ' || REPLACE(phonetic_transcription, 'dɹ', 'ʤɹ')
WHERE INSTR(phonetic_transcription, 'dɹ');

See the demo.

Answered By - forpas

Answer Checked By - Robin (PHPFixing Admin)