Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I have to remove the special characters from the name field and the below are the conditions which are need to be considered. I need the count of those errors.Please find the below sample data and advise how can i achieve this.
Error Count
At the begining and ending should not have special characters.
Inbetween first and last name should not have special character (other than .,)
There are email ID also there in the data instead of names those can be considered as Error.
not Error count
., can have in the first and last name separation.
Names |
- |
, Amit Agrahari |
, Anuradha Iyer |
, Diamond Gong |
, Hemant P. Deore |
, Jules Keghie |
, Vishal Prasad, |
. Deepan. |
. Harshana |
. Martijn |
\Remy Alexander |
21 New Interns |
A D A Ratnam |
A.K. Pandey |
T.Uday |
Expected Result as below
Names | First Name | Last Name | Error Count |
, Amit Agrahari | Amit | Agrahari | 1 |
, Anuradha Iyer | Anuradha | Iyer | 1 |
, Diamond Gong | Diamond | Gong | 1 |
, Hemant P. Deore | Hemant P. | Deore | 1 |
, Jules Keghie | Jules | Keghie | 1 |
, Vishal Prasad, | Vishal | Prasad | 2 |
. Deepan. | Deepan | 2 | |
. Harshana | Harshana | 1 | |
\Remy Alexander | Remy | Alexander | 1 |
21 New Interns | New | Interns | 1 |
A D A Ratnam | A D A | Ratnam | 0 |
A.K. Pandey | A.K. | Pandey | 0 |
.,Uday Krishna* | Uday | Krishna | 3 |
Regards
Kalyankrishna T
Perhaps this?
LOAD Names, Names1, SubField(Trim(PurgeChar(Names1,'0123456789')),' ',1) as FName, SubField(Names1,' ',-1) as LName;
LOAD Names,
If(Match(Left(Names,1),',','.','\', '*') or Match(Right(Names,1), '.', '\', ',', '*'), Trim(PurgeChar(Names,',.\*')), Names) as Names1 Inline [
Names
", Amit Agrahari"
", Anuradha Iyer"
", Diamond Gong"
", Hemant P. Deore"
", Jules Keghie"
", Vishal Prasad,"
". Deepan."
". Harshana"
". Martijn"
"\Remy Alexander"
"21 New Interns"
"A D A Ratnam"
"A.K. Pandey"
".,Uday Krishna*"
];