How to Normalize accent characters in user registration form

[Mohit_Sanka]

Hi,

We have a use case where users can enter accent characters in first name and last name fields on the user registration form but those details should be normalized while generating the user name and email for the user.

Example: firstname: Sáviynt  lastname: Test

Username: Tsaviynt Email: Tsaviynt@outlook.com 

so is there any sql function that we can utilize while generating the user name to normalize the accent characters but save the same in firstname and last name fields.

Thanks & Regards,

Mohit.

[rushikeshvartak]

SELECT CONVERT(firstname USING ASCII) AS normalized_firstname,
CONVERT(lastname USING ASCII) AS normalized_lastname
FROM users;

[Mohit_Sanka]

Hi @rushikeshvartak ,

The above query is converting the accent character to question mark and also we need to do this normalization on the user form it self as the username logic is with the combination of firstname and lastname.

[rushikeshvartak]

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
firstname, 'á', 'a'), 'é', 'e'), 'í', 'i'), 'ó', 'o'), 'ú', 'u'),
'Á', 'A'), 'É', 'E'), 'Í', 'I'), 'Ó', 'O'), 'Ú', 'U'),
'à', 'a'), 'è', 'e'), 'ì', 'i'), 'ò', 'o'), 'ù', 'u'),
'À', 'A'), 'È', 'E'), 'Ì', 'I'), 'Ò', 'O'), 'Ù', 'U'),
'ä', 'a'), 'ë', 'e'), 'ï', 'i'), 'ö', 'o'), 'ü', 'u'),
'Ä', 'A'), 'Ë', 'E'), 'Ï', 'I'), 'Ö', 'O'), 'Ü', 'U'),
'ñ', 'n'), 'Ñ', 'N') AS normalized_firstname
FROM users;

[Mohit_Sanka]

Hi @rushikeshvartak , we tried the above condition but we got to know that the client data has these special characters on other alphabets as well. Its not restricted with vowels only. So is there any other way for us to deal with this?

[rushikeshvartak]

Add all characters