I have a client who is asking me to enable Pinyin searching on an autocomplete input on web app.
The filter hits an Azure SQL database and uses the following where statement.
WHERE countryName LIKE N'%美国%'
This works when the user enters the correct chinese characters. In the above example the input shows the correct country. However in some cases Chinese users want to query using pinyin.
I.e.
WHERE countryName LIKE N'%meiguo%'
**MeiGuo is the pinyin representation for 美国 which stands for United States
I have seen some examples of SQL Server functions which would convert the Chinese characters to pinyin so I could convert both the input and the entire column to western script and do the search but it (A) seems insanely computationally expensive to do this on every record when searching (B) I can imagine some quirks where a search for specific Chinese character will return many records which don't have that character but have the same western characters.
Any ideas on how to do this in a good way?