Hi, we are trying to meet a requirement where the query generated by a user search from an application retrieves strings with diacritics in it along with the other search results. For example, a user searching for 'resume' should return both 'resume' and 'résumé' in the resultset (assuming both these values exist in the column). In other words, the search/query should be 'diacritics insensitive'.
Does anyone know how this can be achieved within SQL or directly at the Teradata database layer?
You could use a nesting of the function translate('résumé' using unicode_to_unicode_nfd) to normalize the combining marks and the oreplace function to remove them. You'll need to list each combining mark in Unicode you want oreplace to remove.
A better way would be create a UDF which calls a function in the ICU API (see Unicode.org) to get the character properties. The ICU library is installed on the server, but currently only used internally by the DBS so the interface would need to be added.