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

Question

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.

forpas · Accepted Answer · 2022-03-08T20:30:31.813

1

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.

edited Mar 08 '22 at 20:30

answered Mar 08 '22 at 20:24

forpas

160,666
10
38
76

As far as I understand, you changed a certain element (dɹ) with another (ʤɹ) but it is different from the expected outcome. the phonetic equivalent of the words should be -the original one / the version where dɹ is changed with ʤɹ- for example dream should be dɹim / ʤɹim not just ʤɹim. Sorry If I wasn't able to clearly convey what I am trying to do. – Aschente Mar 08 '22 at 20:38
@Aschente I know, I have already edited after realizing what you asked for. Check my edited code. – forpas Mar 08 '22 at 20:39

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

1 Answers1