Home > Software design >  How to write the exact text next to the text and replace certain letters in it? SQLite
How to write the exact text next to the text and replace certain letters in it? SQLite

Time:03-09

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.

CodePudding user response:

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.

  • Related