Collating Spanish accents in PL-SQL

The following command in PL-SQL is used to remove latin accents from names in Oracle PL-SQL programming language, it is useful when you have to insert words in Spanish that usually contain tittles.


SELECT NAME_USER FROM USERS
WHERE Convert(LOWER(NAME_USER ),'us7ascii')=Convert(LOWER('JESÚS'),'us7ascii')


The previous query will remove the tittles from the data and convert it to lowercase text, for example 'JESÚS' will be converted to 'jesus' without the tittle on the U letter.

The recommended approach is to format the text when you insert it so that you don't have to do it everytime you search for it, in a small database this might not be a problem but when you're searchings millions or billions of records this formatting will massively decrease performance.

You also have to take into consideration whether the information has to have those tittles for presentation purposes for formal documents and reports. In that scenario the best solution would be to add a second column with the raw data and one with the formatted data, the searches should be done against the formatted data column and it should be the one indexed.