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.