I was initially interested in writing poetry and wanted to create a query which would return a list of words with matching phoneme or similar rhyming words.
For Example: Words rhyming with ‘love’
Word Arpabet
COLGROVE K OW1 L G R AH0 V
DOVE D AH1 V
FOXGLOVE F AA1 K S G L AH2 V
FREELOVE F R IY1 L AH2 V
GLOVE G L AH1 V
The numbers above denote the lexical stress marker and are as follows:
0 — No stress
1 — Primary stress
2 — Secondary stress
The query below sources the text file “cmudict-0.7b comma.txt” from the Carnegie Mellon University Pronouncing Dictionary and parses the data into table 'cmudict' and more specifically into the 'word' field for the list of words and the 'arpabet' field for the phonetic pronunciation of the word.
The txt file used in this query can be downloaded here:
CREATE TABLE cmudict (
word VARCHAR(255),
arpabet VARCHAR(255)
);
BULK INSERT cmudict FROM 'C:/Users/Michael/Desktop/cmudict-0.7b comma.txt'
With (FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a');
The multiple iterations of replace in the where clause is to remove all the lexical stress markers (0,1,2) so if we were to try to find a word that would rhyme with ‘love’, which has the arpabet of ‘AH0 V’ then the list of words with the arpabet containing ‘AH1 V’ or ‘AH2 V’ would be returned because of the similar phonetic or matching row values.
CREATE PROCEDURE rhymingDictionary
@word_input nvarchar(50)
AS
SELECT word, arpabet
FROM cmudict
WHERE REPLACE(REPLACE(REPLACE(reverse(reverse(right(arpabet, 5))),'0',''),'1',''),'2','')
LIKE CONCAT('%',
(SELECT REPLACE(REPLACE(REPLACE(reverse(reverse(right(arpabet, 5))),'0',''),'1',''),'2','')
FROM cmudict WHERE word = @word_input));
exec rhymingDictionary @word_input = 'love'
The output produces the following:
