Click HERE to see how Saviynt Intelligence is transforming the industry. |
08/27/2024 11:50 PM
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.
08/28/2024 12:03 AM
SELECT CONVERT(firstname USING ASCII) AS normalized_firstname,
CONVERT(lastname USING ASCII) AS normalized_lastname
FROM users;
08/28/2024 12:07 AM
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.
08/28/2024 12:20 AM
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;
09/11/2024 05:38 AM
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?
09/11/2024 05:39 AM
Add all characters